Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: How to write set modifier with $ expansion and date variable

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

12 Replies
MVP
MVP

Re: How to write set modifier with $ expansion and date variable

Try this:

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

b_garside
Valued Contributor

Re: How to write set modifier with $ expansion and date variable

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

mov
Esteemed Contributor III

Re: How to write set modifier with $ expansion and date variable

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.

MVP
MVP

Re: How to write set modifier with $ expansion and date variable

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

Re: How to write set modifier with $ expansion and date variable

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

mov
Esteemed Contributor III

Re: How to write set modifier with $ expansion and date variable

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

mov
Esteemed Contributor III

Re: How to write set modifier with $ expansion and date variable

Se attached.

I modified the script and the count text boxes only

Not applicable

Re: How to write set modifier with $ expansion and date variable

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

mov
Esteemed Contributor III

Re: How to write set modifier with $ expansion and date variable

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.

Community Browser