Solved! Go to Solution.
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
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
Hi Anirudh,
Thanks for your replay..
The current set up is more like the following.
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:
And the Blueprint for this is:
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:
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:
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.
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:
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
I think the reason for your issue on the below
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
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...
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.