Creating a Module Report but need to bring detail
Hi Team,
I hope you can help me with a step I am struggling with.
I have Module that have 4 years "Raw Data - Module: Data 00 - All T&E" for Transaction detail (using an Index as a list) and it has many items such as Vendor, Periods, BU, Functions, Account #. And as I need it for reporting purposes, I have bring the Lists with FINDTITEM or Parents, so it can be SUM.
Then as I need the Expense by Vendor, Function, Account and Business - I have created a Second Module and use SUM to bring the values.
Finally - We are creating a Dashboard with this information, where you can see your Total Expense by BU, Function and Account using the REP02 - Module. Then if you click in the Function, then it will bring you the list of vendors (all this works fine).
However, we want to be able also to bring the "Report Name" of that vendor which is store on the "Raw Data MOdule - DATA 00 - All T&E". One vendor can have multiple "Report name" because it is the description of the Transaction - The idea is to be able to click the "Vendor Name" from REP02 Module (once in the dashboard) and synchronized to bring the "Report Names" and the "Expense Associated"
Hope you can help me and point me in the right direction. Thanks!
Answers
-
Thank you for providing so much detail and all of the screenshots! I'll apologize in advance for asking for one more screenshot... would you mind attaching one that shows the end result of the "CC Description" field?
I imagine you will need to use some combination of the FIND, LEN and RIGHT/LEFT/MID formulas to extract specific text values from your CC Description field which can become your Report Name field. This formula will depend on how this CC Description field comes in - often for us it's about finding the placement of the underscore within the concatenated field, and from there you can come up with a formula that pulls in what you want. If you could attach at least an example of what one of these CC Description fields looks like, it would help figure out exactly how this formula should be written.
One other note, and sorry for the unsolicited advice on this front as I know you aren't asking about this portion, but your formula for "Pcard Data" in the REP02 module that you attached a screenshot of has a combination of LOOKUP and SUM. It is best practice to avoid combining SUM and LOOKUP in the same formula wherever possible as it is very straining on the calculation engine. It is often best to break these formulas up in 2, with one intermediate formula performing the SUM and then the result line performing the LOOKUP based off of that. For further detail, view here: Link
Thanks,
Timmy0 -
Sorry, LENGTH* formula not LEN. Excel habits...:)
0 -
Hi Timothy,
Thanks for taking the time to look at it 😃
Actually what I want to show if the "Report Name" Line Item that is on DATA00 Module. Because one Vendor in a specific period of time can have multiple "Report Name", meaning that have multiple transactions. It could be that we bought it because of Event 1and that same period we bought it because of Event 2, so the "Event 1 and Event 2" are the Report names under the same vendor. Hope it makes sense.
So I need to find a why to linked I guess the REPORT 002 Module with the DATA00 Module, or create another module to bring those details?
Thanks for the "SUM & LOOKUP" - It is on my checklist to change it, but I was first trying to build the high level report.
0 -
Hi Yordana @CommunityMember132037 ,
Ah, I see - apologies for my misunderstanding. I think I know a solution, though I have a feeling that someone more experienced might have something that will perform better.
You can use the TEXTLIST function to pull in all of the different Report Names for an individual Vendor per time frame. Basically, the formula will look like: 'DATA00 - ALL T&E'.Report Name[TEXTLIST:*Vendor List Lookup line item*,TEXTLIST:*Quarter Lookup line item*]. This should pull in all report names as a comma separated list by time period. I would recommend doing this in a separate module with only the dimensions that are absolutely necessary, so likely just Vendor and time frame.
The reason I hesitate to advise this is that the TEXTLIST function is super taxing on the calculation engine and it is generally best practice to avoid it whenever possible. I would recommend reviewing this article, and specifically Rob Marshall's reply: Link
There may be a way around using TEXTLIST here by utilizing ANY... though I am struggling to think through without the model right in front of me. So if anyone else has an idea of a way around it, do let me know!
I'll attach a couple screenshots of the formula in use in a dummy model that I made. Let me know if any more detail is needed!
Thanks,
Timmy1 -
Don't be sorry, LEN() works as well 😉
Anaplan allows you to type in editor both LENGTH() and LEN() (with the latter being translated to LENGTH() once you submit the formula), the same for few others, I think complete list:- "NOT(X)" => "NOT X"
- "OR(X,Y,[...])" => "X OR Y [OR ...]"
- "AND(X,Y,[...])" => X AND Y [AND ...]"
- "IF(X,Y,Z)" => "IF X THEN Y ELSE Z"
- "LEN(X)" => "LENGTH(X)"
2 -
I will give it a try and let you know how it goes! 😃
Thanks for the help on this!
Regards,
0 -
@CommunityMember132037 If I understand correctly, you would like to see synchronized, what you choose in REP02 module (which shows aggregated data from the module DATA00) the detailed rows of the module DATA00.
Unfortunately, there is no way to reference the current element chosen in REP02 to use it as a filter directly in module DATA00.
The native synchronization works in Anaplan from parent to its children...or for elements of the same list, if the lists are in Page.
In order to have native sync for detailed data from DATA00, you could have a separate module DATA00b where to add the lists in Apply To all the lists from REP02...and ha a line-ite filter with formula like Item('C5 Cost Center') = CC Cost Center Description...
All the other line-items replicated from DATA00 could have in apply To only the Transactional list.
That Filter line-item could generate huge nr. of cells as it is the multiplication of Transactional data list X Cost Centers list X Vendors X ...etc.. other lists from REP02.
Another way is to create Users filtering directly on DAT00 and let the user to choose manually what elements to filter and show the DATA00 based on the filter chosen...but this will be disconnected with elements chosen in REP02.
Hope it helps
Alex
2 -
Below an example of synchronization between REP02 and DAT00b using 2 lists: Vendors and Cost-Center.
If you need synchronization only for Vendors, just use Vendors list in DAT00b module "Apply To":
DAT00b blueprint:
Hope it helps
Alex
1
