2 Replies Latest reply: Apr 13, 2017 7:22 AM by Shamit Shah RSS

    Variables and KPI object

    Shamit Shah

      I am currently pulling data from QlikView into Qlik Sense and I have the following in QlikView script:

      [Wk Start],

      Date(Date#([Wk Start], 'YYYYMMDD')) as WkStartDate,

      Date(MonthStart(Date#([Wk Start], 'YYYYMMDD')), 'MM-YYYY')  as WkStartMonthYear,

      Month(Date#([Wk Start], 'YYYYMMDD'))  as WkStartMonth,

      Year(Date#([Wk Start], 'YYYYMMDD'))  as FYBIKPIYear,

      if(Month(Date#([Wk Start], 'YYYYMMDD')) > 6, Year(Date#([Wk Start], 'YYYYMMDD'))+1, Year(Date#([Wk Start], 'YYYYMMDD'))) as WkStartFiscalYear,

      dual(month(Date#([Wk Start], 'YYYYMMDD')),month(addmonths(Date#([Wk Start], 'YYYYMMDD'),6))) as WkStartFiscalMonth;


      I created variables in Qlik Sense (similar to the ones in Qlik View) :




      vCurrentweek =sum({<WkStartDate= {"$(=Date(Max(WkStartDate, 'YYYYMMDD')))"}>}[SOH Value])


      vPriorweek=sum({<WkStartDate= {"$(=Date(Max(WkStartDate, 'YYYYMMDD')-7))"}>}[SOH Value])


      I tried the expression below in a KPI object but it does not work:


      =Sum ({<[Wk Start]={"<=$(vMaxWkStartDate)"}>} [SOH Value])


      I tried to use the next two variables in an expression to calculate the change in the week but that also did not work:


      =Sum ({<[Wk Start]={"<=$(vCurrentweek)"}>} [SOH Value])-Sum({<[Wk Start]={'<=$(vPriorWkStartDate)'}>} [SOH Value]))



      The format of the WkStartDate in the file is YYYYMMDD


      Appreciate your assistance.