Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to write set modifier with $ expansion and date variable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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))

View solution in original post

12 Replies
sunny_talwar

Try this:

SET vCurrentMonthYear = Date(Max(CallMonthYear), 'MM/YYYY');

b_garside
Partner - Specialist
Partner - Specialist

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])

Anonymous
Not applicable
Author

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.

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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))

Anonymous
Not applicable
Author

Se attached.

I modified the script and the count text boxes only

Not applicable
Author

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

Anonymous
Not applicable
Author

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.