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) :

       

      vMaxWkStartDate=Max(WkStartDate)

       

      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.