16 Replies Latest reply: Aug 1, 2018 4:40 AM by Helge Jørgensen RSS

    Set analysis - sum of last 30 days

    Helge Jørgensen

      Qlik experts,

       

      I do know that this question has been asked several times here on the community, but I just can’t get it to work.

       

      I would like to use a KPI to show a measure of the last 30 days. I have used this expression:

       

      sum({<ElementID= {'RR'}, Dato = {">$(=Dato(Today()-30))"}>}[Vaerdata.Verdi])

       

      Dato is my date filed (Norwegian)

       

      The problem is that the expression does not show the last 30 days, but the entire record of data (several years).

       

      Can someone please help me?

       

      And is there a post or blog where I can read more about how to use Date-function in Qlik sense?

       

      Cheers

      Helge

       

      Here is the Calander-script that I use:

       

      QuartersMap:

      MAPPING LOAD 

      rowno() as Month,

      'Q' & Ceil (rowno()/3) as Quarter

      AUTOGENERATE (12);

          

      Temp:

      Load

      Min(Dato) as minDate,

      max(Dato) as maxDate

      Resident Vaerdata;

          

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;

          

      TempCalendar:

      LOAD

      $(varMinDate) + Iterno()-1 As Num,

      Date($(varMinDate) + IterNo() - 1) as TempDate

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

          

      MasterCalendar:

      Load

        TempDate AS Dato,

        week(TempDate) As Week,

        Year(TempDate) As Year,

        Month(TempDate) As Month,

        Day(TempDate) As Day,

        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

        WeekDay(TempDate) as WeekDay

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;