Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
];
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)
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,
];
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,
];
Thank you. I guess Qlik can have 'special' datatypes and that is why myDATES table did not work at first.