Max Month in Set analysis when month field has dual value.

[Anonymous]

Hi ,

I have a month field that has text value like 'Jan, Feb, Mar' etc. In order to sort it properly in a list box, I used a dual() function to assign it a numerical value. However, my set analysis with Max Month stops working....

When more than one months are selected, I want the sum(amount) for the biggest month. 

I created a variable vMaxMonth = Max(Month) and use this expression to calculate the biggest month's total amount with this expression: =sum({<Month={$vMaxMonth}>}Amount)

However it returned 0 instead of 300. see screenshot below and attached pvw file.

Can someone help with that?

Thanks

Zixiao

[swuehl]

You can probably use a numeric comparison in a search:

=sum({<Month = {">=$(vMaxMonth)"} >}Amount)

Alternatively, create your variable like

=FirstSortedValue(Month, -Month)

[ToniKautto · Accepted solution]

The variable vMaxMonth does not have a value.

Try using the expression below.

=sum({<Month={"$(=MaxString(Month))"}>}Amount)

[ramoncova06]

your variable does not have anything in it you might want to check it

as swuel already suggested you need to use search, though if you only want the max selected month do something like this

=sum({<Month={"<=$(=$(vMaxMonth))>=$(=$(vMaxMonth))"}>}Amount)