Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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

12 Replies
b_garside
Partner - Specialist
Partner - Specialist

Is that an oversight or a feature to come?

Anonymous
Not applicable
Author

Most likely neither.  You can of course submit an idea.

Anonymous
Not applicable
Author

I just tried another approach, without using the MonthYear field (or dimension) at all:

Count({<DateTime={"=floor(MonthStart(DateTime))= $(=$(vCurrentMonthYear))"}>} Amount)

I think this is the way to go in cases where the field (like MonthYear here) doesn't exist.