How to find MAX number on a specific Dimension

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

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

Can you share the screenshot with us?

@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!

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

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

@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

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.

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

ManjunathKN