Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am somewhat new to Qlik Sense and not really a code guy, but typically I can figure things out by looking at forums such as this.
What I am trying to accomplish is a KPI that shows sales for the month.
When I do this formula (for the year), everything works out fine and I get my yearly total.
Sum({<CALENDAR_YEAR={$(=vCurrentYear)}>} SumOfGROSS_SALE_AMT)
However, when I do this, I get nothing.
Sum({<CALENDAR_YEAR={$(=vCurrentYear)},{NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)
Note, I have in my data load editor the following
LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));
LET vCurrentMonth = Month(Makedate(2016,3,31));
LET vCurrentYear = NUM(year(Makedate(2016,3,31)));
Can anyone help me see what I may be doing wrong?
I would suggest you to create a Month field in the script and then may be try this:
Sum({<CALENDAR_YEAR={$(=vCurrentYear)}, MONTH_FIELD={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)
LOAD MONTH(TRANS_DATE) as MONTH_FIELD
I would suggest you to create a Month field in the script and then may be try this:
Sum({<CALENDAR_YEAR={$(=vCurrentYear)}, MONTH_FIELD={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)
LOAD MONTH(TRANS_DATE) as MONTH_FIELD
LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));
That's a rather complex way to basically do this: LET vCurrentMonthNum = 3;
To answer your real question:
You can only use real field names on the left side of the = in a set analysis expression. What you should do is first create a Month field in the data load editor:
LOAD
...lots of fields,
Num(Month(TRANDATE)) as CALENDAR_MONTHNUM
FROM ...
Then your expression can be changed to
Sum({<CALENDAR_YEAR={$(=vCurrentYear)},CALENDAR_MONTHNUM={$(=vCurrentMonthNum)}>} SumOfGROSS_SALE_AMT)
Try this
Sum({<CALENDAR_YEAR={$(=vCurrentYear)},NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)