How to find MAX number on a specific Dimension
Need some help on a logic wherein I need to show Maximum number for a specific Dimension out of 5 Dimensions, for e.g. in the below screenshot I need to bring 49,258 from Line Item "A" (as highlighted) which is contributed by Tennessee (which is one of the States in States Dimension, on columns) on LI "MAX of All States" so that I can divide the respective States' numbers by the maximum of all States (which is Tennessee in this case, which is 6300 / 49258, 7088 / 49258 and so on..). But the challenge here is when applying MAX on as Summary method on LI "MAX of All States" its showing Maximum of "Products", "Accounts" and "Periods" (custom month's list) dimension but within the respective states.
Now the ask is I need to show Maximum value out of all the states when I go within the page selectors.
If you want to take the values at top levels of each dimension then I would request you to **** all the dimensions of the target line item i.e., have only States as a dimension in that line item.
Solution should be like this
1. Create a different module with just STATES as a dimension.
2. Refer to A line item into this new module from the existing module by using the formula A[MAX:States from SYS module]
3. Use the division in Module 1 and Module 2 to arrive at the correct values.
Yes, we certainly can. It's just between Three SELECT statements vs Auto aggregation, I am not sure which one is going to perform better but it certainly can be achieved both ways.0
The problem is I cant use Select because I don't require aggregation at the top-level, requirement is, I need to see the Maximum value among all the states, but at any level of Products, Accounts and other dimensions.
Hope this clarifies!0
Sorry for multiple edits, it got bit tricky.
Let me know if below solution helps.
All products, All Accounts
Product 1, All accounts
Product 1, Account 1
1 staging formula
A[SELECT: Products.All products, SELECT: Accounts.All Accounts, SELECT: TIME.All Periods]
Wow... what a solution!!! I am still processing all the calculations which are being used to solve this problem. Awesome.
I'll try to recreate this problem and the solution for my learning.0
Use the MAX aggregation function as suggested by MIsbah but you dont have to use a separate module.
Remove the States dimension from the 'MAX of All States' and 'A/Max of all States' line items.
The max function should look like A[Max:States List]. This will only pull through the maximum value present across the States dimension but will only work if the source dimension is not present in the target and the reason why you need to remove it from these line items. You can do this in the blueprint by pressing delete in the applies to section.0
What an amazing solution Manjunath, I am able to get the results at Total and lowest levels but now the challenge is on the Period (Custom month List which has Total Period as Top level and FY, HY, Qtr and Month as children lists) dimension which I am using in the module, although, we did a SELECT on Total Period and got monthly numbers (which is at the lowest level of Periods dimension) on a separate LI but I'm not getting MAX numbers at the FY, HY and Quarterly levels which are in between Total Period and Monthly levels, since we used Select function on Total Periods.
But, many thanks for this great solution, it definitely gave me a path to move ahead!
Thanks to other experts, as well!
Glad that it helped you in someway .
If you are using custom time period it will get more tricky as there is no time summary. My quick thought here is; can we calculate in original time period and map it through a system module (native to custom time mapping)