Creating a Module Report but need to bring detail

CommunityMember132037
CommunityMember132037
in Modeling

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.

 

CommunityMember132037_0-1646937480899.png 

CommunityMember132037_1-1646937590890.png

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. 

 

CommunityMember132037_2-1646937670904.png

CommunityMember132037_3-1646937713447.png

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).

CommunityMember132037_5-1646938299631.png

 

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" 

CommunityMember132037_4-1646938050205.png

 

Hope you can help me and point me in the right direction. Thanks!

 

 

Tagged:

Answers

  • TimothyThomas
    TimothyThomas

    Hi @CommunityMember132037 ,

     

    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,
    Timmy

  • TimothyThomas
    TimothyThomas

    Sorry, LENGTH* formula not LEN. Excel habits...:)

  • CommunityMember132037
    CommunityMember132037

    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.

  • TimothyThomas
    TimothyThomas

    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,
    Timmy

  • M.Kierepka
    M.Kierepka

    @TimothyThomas,

    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)"
  • CommunityMember132037
    CommunityMember132037

    Hi @TimothyThomas 

     

    I will give it a try and let you know how it goes! 😃

    Thanks for the help on this!

     

    Regards,

     

  • alexpavel
    alexpavel

    @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

     

  • alexpavel
    alexpavel

    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":

     

    alexpavel_0-1647349519553.png

     

    DAT00b blueprint: 

    alexpavel_1-1647349599928.png

     

    Hope it helps

    Alex

Categories