How to find MAX number on a specific Dimension

Ishan

Hello Experts,

 

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.

 

Kindly help!

IS

 

 

Misbah

@Ishan 

 

Try using this formula A[MAX: State List] where State list is the list formatted line item from SYS State module.

 

Thanks,
Misbah

Miz Logix

Ishan

@Misbah Thanks a lot for the reply, unfortunately its showing the same results as its showing currently in the "MAX of All States" LI (screenshot). Seems it'll work through Rank but I tried many ways and couldn't reach to the desired results. 

Misbah

@Ishan 

Can you share the screenshot with us? 

Ishan

@Misbah 

Here it is, so the highlighted line item is the one with MAX logic (with MAX as summary method), and the one above with the name "MAX of All States" is just a simple reference with MAX summary.

 

Thanks!

Misbah

@Ishan 

 

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.

 

Thanks,

Misbah

Miz Logix

ManjunathKN

@Misbah @Ishan 

 

Can we not use SELECT function with top level for all dimensions excluding state dimension? in separate line item and then do MAX on states list in another line item to achieve this?

Thanks,
Manjunath

 

 

Misbah

@ManjunathKN 

 

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.

Ishan

@Misbah @ManjunathKN 

 

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!

ManjunathKN

@Ishan 

 

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

Blueprint view:

 

1 staging formula

A[SELECT: Products.All products, SELECT: Accounts.All Accounts, SELECT: TIME.All Periods]

 

Thanks,
Manjunath

 

 

 

ShubhamCh

@ManjunathKN 

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. 

ChrisAHeathcote

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.

Ishan

@ManjunathKN 

 

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!

 

Kind regards,

IS

ManjunathKN

@Ishan 

 

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)

 

Thanks,

Manjunath