Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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.