Can i select max month in Set Analysis with Alternate States?

[saveriospa]

Hi to all.

I've defined the alternate state "StateB" and an expression like :

sum({<KPI={'Production'},Period=StateB::Period, Quarter=StateB::Quarter, Month=StateB::Month>} Actual)

If i need to filter Month for the maximum month selected by the user, how can i do?

Many Thanks!!

Saverio

[swuehl]

You can do it similar to selecting a max month in default state:

sum({<KPI={'Production'},Period=StateB::Period, Quarter=StateB::Quarter, Month= {$(=Max({StateB} Month))}>} Actual)

If Month is a dual value with text and numeric representation, you might need to format the outcome of the max() function to match the text representation. (You can try something like

Month= {$(=FirstSortedValue({StateB} Month,- Month))}

[saveriospa]

Thanks for your quick response swuehl !

I've already a field that represent the numeric value for the month (Month#) and than i apply your tip.

But, if i try to do :

sum({<KPI={'Production'},Period=StateB::Period, Quarter=StateB::Quarter, Month#={$(=Max(Num({StateB} Month)))}>} Actual)

It does not return the results that I want.

Can you tell me where I'm wrong?

Thank you in advance!

[sunny_talwar]

Try this:

Sum({<KPI={'Production'},Period=StateB::Period, Quarter=StateB::Quarter, Month#={$(=Max(Num({StateB} Month#)))}>} Actual)

[swuehl]

I think you need to push the set expression to the max() aggregation function:

sum({<KPI={'Production'},Period=StateB::Period, Quarter=StateB::Quarter, Month#={$(=Max({StateB}  Num( Month)))}>} Actual)

I also think that you don't need the Num() function since max() will operate on the numeric representation anyway.

If this doesn't work out, could post what you get and what you expect (as screenshots, but better by posting a small sample QVW)?

[saveriospa]

This is a small example.

Thanks again!

[swuehl · Accepted solution]

But it seems to work for me using

Sum({<Month#={$(=Max({StateB}Num( Month)))}, Period=StateB::Period, Quarter=StateB::Quarter>}Sales)

edit:

and even without the num():

Sum({<Month#={$(=Max({StateB} Month))}, Period=StateB::Period, Quarter=StateB::Quarter>}Sales)

[saveriospa]

Yes, you are right swuehl!

I had forgotten to define in the expression of the original application all fields related to time dimension (Period = StateB :: Period, Quarter=StateB::Quarter ....).

All work now

You are great. Thanks a lot!!!!