10 Replies Latest reply: Feb 22, 2017 3:49 PM by Sunny Talwar RSS

    Trouble with using date variables in set analysis

    Bradley Wilson

      I am trying to set up some dashboards that will automatically update to current month/year and previous year/month, so i have tried to set up some variables to do those calculations

       

      My current month variable is set up as:

      vCurMonth = monthname(addmonths(today(),0))

       

      I used the variable pane under the edit mode for sheets to create this as opposed to doing it in the load script (not sure if this matters)

       

      The formula i am trying to use in a KPI for a monthly total is:

      Sum({$<[OINV.DocDate.autoCalendar.YearMonth]={$(vCurMonth)}>} [LineTotal])

       

      But it resolves as "-"

       

      When I put Max([OINV.DocDate.autoCalendar.YearMonth]) in the KPI it evaluates to 2/1/2017 and when I put $(vCurMonth) in the KPI it evaluates to 2/1/2017 too.  So I'm just confused why it won't work.  Many of the discussions i've found say to put quotes around the variable, but that doesn't work either.

       

      Does this not work in Qlik Sense or am i messing up some syntax somewhere? [LineTotal] is in a different, but directly connected, table from OINV, if that matters.

       

      Thanks