Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm relatively new to Qlik and I've been try to do some time period analysis (current vs. prior month), and I can get things to work with with a literal date value in the set modifier. However, when I try to use a variable containing the current and prior period I can't get it to work. I have included the relevant snippets from the load script and measure expression below. Any pointers would be greatly appreciated. I have read numerous online posts, PDFs (ex. DateFields-TechBrief-US_v3.pdf) and blogs, all of which have provided very useful insights but I'm still unable to get what seems like a simple express to work.
LOAD
<snip>
Timestamp("Call Start Date/Time") as "CallDateTime",
Date(MonthStart(Floor("Call Start Date/Time")),'MM/YYYY') as CallMonthYear,
<snip>;
set vCurrentMonthYear = Max(CallMonthYear);
set vPriorMonthYear = AddMonths(Max(CallMonthYear), -1);
KPI Expression (this one works):
Count({<[CallMonthYear]={'02/2015'}>}[Call Cost])
KPI Express (this one does not work):
Count({<[CallMonthYear]={"=$(vCurrentMonthYear)"}>}[Call Cost])
I've tried what seems like an endless number of variations on the $ expansion and variable construction with no success.
Thanks in advance for any insights you can provide!
Tom
It doesn't work because MonthYear is not a field. Create this field in the script:
LOAD *, floor(DateTime) as MonthYear Inline [DateTime, Type, Amount
...
And your set should be fine.
Edit: floor(MonthStart(DateTime))
Try this:
SET vCurrentMonthYear = Date(Max(CallMonthYear), 'MM/YYYY');
Tom,
you may only be missing or misplaced the = equal in your expansion. You can do this by inserting = prefix prior to the variable during load Also Sunny is right about needing the Date function to format as a date result
set vCurrentMonthYear = Date(Max(CallMonthYear));
Try this;
KPI Express
Count({<[CallMonthYear]={"$(=vCurrentMonthYear)"}>}[Call Cost])
The problem is that the statement
set vCurrentMonthYear = Max(CallMonthYear);
creates variable in the integer format - because of max(), while the CallMonthYear field has format 'MM/YYYY'. In set modifier, format must match. So, you can either change definition of the variable (as per sunindia, or explicitly define format in set.
Hi,
I think the issue is with the Date format, when you use Max() it will return date in number format, so you have to use Date() to convert to the required format
set vCurrentMonthYear =Date(Max(CallMonthYear), 'MM/YYYY');
set vPriorMonthYear = AddMonths(Max(CallMonthYear), -1);
KPI Express (this one does not work):
Count({<[CallMonthYear]={"$(=vCurrentMonthYear)"}>}[Call Cost])
Hope this helps you.
Regards,
Jagan.
Thank you all for your responses and you might be 100% correct, but I've tried different formatting for the variables and source data with no luck. I've created a very simple Qlik Sense application to demonstrate my issue. The app performs an inline load of 7 records, defines 2 variables, and the sheet displays the contents of the data, variables and contains a few aggregations with some set analysis. I have labeled the KPIs with the expect results.
Again, thank you for any insights you can provide!
Tom
It doesn't work because MonthYear is not a field. Create this field in the script:
LOAD *, floor(DateTime) as MonthYear Inline [DateTime, Type, Amount
...
And your set should be fine.
Edit: floor(MonthStart(DateTime))
Se attached.
I modified the script and the count text boxes only
Michael,
Thank you, that corrected my problem! Now, can you help me understand why it didn't work with MonthYear defined as a dimension in the Master Items?
Tom
I'm new to Sense myself... Apparently set can work only with the actual fields, not with calculated dimensions. I made some changes in expressions, but the main problem was the dimension itself.