topic Re: Max Month in Set analysis when month field has dual value. in QlikView

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

Anonymous — Tue, 09 Jun 2015 22:34:28 GMT

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

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

swuehl — Tue, 09 Jun 2015 22:54:55 GMT

You can probably use a numeric comparison in a search:

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

Alternatively, create your variable like

=FirstSortedValue(Month, -Month)

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

ToniKautto — Tue, 09 Jun 2015 23:05:00 GMT

The variable vMaxMonth does not have a value.

Try using the expression below.

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

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

ramoncova06 — Tue, 09 Jun 2015 23:07:28 GMT

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)