Sum one Line Item based on the value of another Line Item

gordon_sneddon
gordon_sneddon
in Modeling
Hi,
 
I'm wondering is someone could help us with the following:
 
We have a transaction module called "Data Store" which is made up of line items (columns) and a Transaction Unique Key (List - Rows)

Refer "Image 1" for the Pivot View...
 
Refer "Image 2" for the Model view. (This is a subset of the data contained in the module, first column being the unique ID)
 
There are 84 line items in the model, for each Unique Key there and there are 20 line items of which only one line item per Unique Key is populated (currently with a value, which is duplicated from the "Amount" line item), leaving 19 line items not populated resulting in a large amount of sparsity.
 
In order to reduce the size of the module we would like to reduce the 20 line items down to one line item, with the new line item containing the name of old line item (or something like that), let's call the new line item "Category".
 
In another module, which already exists, we would like to be able to sum up the amount from the Data Store module based on time period, branch name, division and category (there is one more line item which I have left out of the examples as it will work the same way as the branch name and division etc and not required, however handy to know it exists). Something else to note is the formula is to be written in a line item which is not list formatted, it's just a line item, which cannot easily be changed.
 
Normally we'd use a formula such as:
 
Data Store.Amount[SUM: Data Store.Time Period, SUM: Data Store.Branch Name, SUM: Data Store.Division, SUM: Data Store.Category]
 
However this will not work as "SUM: Data Store.Category" cannot go down that one step further (to something like: "SUM: Data Store.Category.CAT1" for the contents of the cells. Even using a SELECT won;t go down to that level, otherwise that would be ideal.
 
We could create an intermediate module with a list formatted Dimension for the Category, however there are 5 dimensions, then create the formula to read from that, however even when sub setting the dimension lists the cell count of the intermediate module is larger than the cell count of the Data Store.
 
Has anyone come across this sort of issue before and how did you manage to resolve it ?
 
Tagged:

Best Answer

  • anirudh
    anirudh
    Answer ✓

    Hi Gordon,

     

    This is achievable quite easily! All you have to do is create a line item subset using the line items in the Report - Current module. Screenshots below:

    image.png

     

     

    image.png

     

    Then in the Data Store Improved module create a line item called LIS Category with the format as LIS - Categories and use this formula: FINDITEM('LIS - Categories', Category)

     

    Finally, back in the Report - Current module (no need to create an intermediate module) use the formula in all 20 of the cat line items:

    'Data Store - Improved'.Amount[SUM: 'Data Store - Improved'.eBranch, SUM: 'Data Store - Improved'.eDivision, SUM: 'Data Store - Improved'.Time Period, SUM: 'Data Store - Improved'.LIS Category]

     

    Please try this out and it should solve your problem!

    More info on Line item subsets: https://help.anaplan.com/anapedia/Content/Modeling/Dimensions/Line_Item_Subsets.html

     

    Regards,

    Anirudh

Answers

  • anirudh
    anirudh

    Hi Gordon,

     

    If I'm understanding the issue correctly, you have 20 line items each containing the category and an amount against the category. Does the Data Store module look something like this..? I have tried to replicate for only 2 line items

     

    image.png

     

    If yes, create 3 line items:

    Combined Category (Text Format)

    Combined Category List (Formatted with the Category list)

    Combined Amount

     

    For Combined Category input this formula - Cat1 & Cat2..... (extend this to all 20 line items)

    For Combined Category List input - FINDITEM(Category List, Combined Category)

    For Combined Amount - Amt1 + Amt2..... (extend this to all 20 line items)

     

    Then in your other module, use the formula: Data Store.Combined Amount[SUM: Data Store.Time Period, SUM: Data Store.Branch Name, SUM: Data Store.Division, SUM: Data Store.Combined Category List]

     

    If there is no overlap between any of the 20 line items, this can be used as a solution

    Please let me know if this helps

     

    Regards,

    Anirudh

  • gordon_sneddon
    gordon_sneddon

    Hi Anirudh,

    Thanks for your replay..

    The current set up is more like the following.

    Current.png

    Where the data store has  'Branch', 'Division', 'Time Period', 'Description' and 'Amount' line items, it then also has number of other line items which will only be populated with a value (from the Amount line item) when the description is meets a criteria (i.e. If the Description is Product1 then Line item Cat1 is populated with the value from the Amount line item and all other Catx line items contain zero, when the description is Product2 then line item Cat2 is populated with the value from the amount line item and all other Catx line items contain zero.

    The module that is looking up the Data Store looks like:

    Report_Current_Result.png

    And the Blueprint for this is:

    Report_Current_Blueprint.png

    Which uses a formula of:

    'Data Store - Current'.'Cat1'[SUM: 'Data Store - Current'.Branch, SUM: 'Data Store - Current'.Division, SUM: 'Data Store - Current'.Time Period]

    With only the 'Catx' number before the opening square bracket changing for the line item formula's.

     

    I'd like to reduce the size of the data store module by turning changing it to something like:

     

    Preferred.png

    Where I have 1 Category Line Item, containing the Category and I get the value from "Amount" Line Item.

    However when I use a similar formula to the one above, I get:

    Report_Improved_Result.png

     

    As I'm not able to get the formula to distinguish between the individual categories (The formula is only in the Cat1 line item (that's why the others show zero) the amount is summed up no matter what the Category is ? (i.e. you see in the current report that the total for all 4 categories added together in May is 550, however in the improved report each line item with the formula would contain 550.

     

    I can use an intermediate module, which would turn Category into a Dimension however this would use more cells that the extra line items in the Data Store.

    And where the result is to end up is in a current module and is a line item.

     

    Hope that make a little more sense and once again, thanks for your assistance.

     

  • DavidSmith
    DavidSmith

    @gordon_sneddon 

    I think the reason for your issue on the below2019-06-24_09-50-26.png

     is Category is not a formatted list.  In the target module, it is easier to have Category as a list and add this to the SUM options

    However, if you are re-engineering the file there are a few more things to do to make this the most efficient set up:

    1. Use a code to define the combinations of Branch, Division, Category (Don't include the Time period! - This is critical, I'll explain why later)

    2. Import the codes into a list (no list properties)

    3. Import non time based values into module dimensioned by the list from 1

    4. Create line items formatted as text to hold Branch, Division and Category and use LEFT, MID, or RIGHT to derive these from the code.  This is more efficient that importing and storing the text values

    5.Create line items formatted as lists for Branch, Division and Category and use FINDITEMs to calculate these from the field in 4

    6. Create a module dimensioned by the list in 1 and time and import the Amount value into it

    7. Now use this module and the formatted list items from 5 to sum into your target module

    8. It would also be better if the modules from 3 and 6 are held in a data hub and the values imported directly rather than using a SUM

     

    Using this technique will reduce the size of the data structures substantially.  So far we have seen 80-90% reductions in the size of the transaction list and data modules.  Also using SUM against a Time period on large lists is also not a very efficient calculation structure

     

    We will shortly be publishing this best practice more fully, but we have seen very positive results so far, so I wanted to let you know.  I outlines this technique in my blog about sparsity 

    https://community.anaplan.com/t5/Blog/The-Truth-About-Sparsity-Part-1/bc-p/44584

    https://community.anaplan.com/t5/Blog/The-Truth-About-Sparsity-Part-2/bc-p/44778

    David

  • gordon_sneddon
    gordon_sneddon

    Hi Anirudh and David,

    Thank you both for your reply's.

    Anirudh - I was implementing your suggestions from your reply when I realised I have category as list in my actual modules, however did not have it as a list in the samples I placed here (as also pointed out by David). However using Anirudh's logic and a slight modification I've been able to make it work. Obviously I couldn't post the actual modules due to the data they contain.

    David - I'm offsite today, however will look into your suggestions also and provide feedback and accept as solutions over the next two days.

    Appreciate both your help. And will get back to you...

  • gordon_sneddon
    gordon_sneddon

    David,

    Anirudh's answer allowed me to solve my immediate issue and reduced the modules cell count by slightly over 35%. I saying that though, I have slotted time in the near future to look further into your recommendations and reviewing your blog.

    Thank you both for your help.

Categories