12 Replies Latest reply: Jun 3, 2015 1:32 PM by Michael Solomovich RSS

    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