Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
tomdabek
Contributor III
Contributor III

using month in set analysis

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

1 Solution

Accepted Solutions
sunny_talwar

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,

];

View solution in original post

4 Replies
sunny_talwar

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)

tomdabek
Contributor III
Contributor III
Author

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,

];

Capture.PNG

sunny_talwar

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,

];

tomdabek
Contributor III
Contributor III
Author

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