topic Re: using month in set analysis in QlikView

using month in set analysis

tomdabek — Fri, 10 Nov 2017 13:53:27 GMT

I am having a problem showing the values associated with the month the user has selected in the Qlikview application.

A chart has expressions that are supposed to show month volume (based on the 'greatest' month the user has selected).

So if the user has selected March, we should see the volume from March.

If the user has selected the months January, February AND March, still we should see only the value for March.

The next expression shows the Year-to-date volume, regardless of the user selection of month.

This 2nd expression works fine:

=sum({$<year={$(=max(year))},month=>}_volume_total)

I can't get the volume for the month however. Nothing seems to work.

The expression i think i should use is:

=sum({$<year={$(=max(year))},month={$(=max(month))}>}_volume_total)

But it does not work.

when the month March is selected, i can see in a text box that:

month=Mar

max(month) = 3

How can i create an expression that can determine the 'maximum' month selected by the user?

so i think my problem is i don't have a numeric value in the database for the month so my expression fails (it always returns zero)

Thanks in advance

Re: using month in set analysis

sunny_talwar — Fri, 10 Nov 2017 13:59:47 GMT

Why don't you create a numeric Month field?

Num(Month(DateField)) as NumMonth

Alternatively, try this

=Sum({$<year={$(=max(year))}, month={"$(=Month(Date#(Max(month), 'M')))"}>}_volume_total)

Re: using month in set analysis

tomdabek — Fri, 10 Nov 2017 14:15:32 GMT

So I tried the numeric month field but for some reason it does not work.

The value i get when i display month_num is null, or '-'

I don't get it...Here is what i had done:

myDATES:

LOAD * Inline

[

month, month_num,

Jan,1,

Feb,2,

Mar,3,

Apr,4,

May,5,

Jun,6,

Jul,7,

Aug,8,

Sep,9,

Oct,10,

Nov,11,

Dec,12,

];

Re: using month in set analysis

sunny_talwar — Fri, 10 Nov 2017 14:19:00 GMT

Try this

myDATES:

LOAD Month(Date#(month, 'MMM')) as month,

month_num;

LOAD * Inline

[

month, month_num,

Jan,1,

Feb,2,

Mar,3,

Apr,4,

May,5,

Jun,6,

Jul,7,

Aug,8,

Sep,9,

Oct,10,

Nov,11,

Dec,12,

];

Re: using month in set analysis

tomdabek — Fri, 10 Nov 2017 14:33:01 GMT

Thank you. I guess Qlik can have 'special' datatypes and that is why myDATES table did not work at first.