7 Replies Latest reply: Apr 5, 2018 8:37 AM by Pascal Dijkshoorn RSS

    Select data the day before dimension date in set analysis

    Pascal Dijkshoorn

      Hi everyone,

       

      I need a formula that selects data from the day before the shown dimension date on the actual dimension date. 

       

      I made an example of what I need in the table below. I should mention that the formula i want to create in the end takes data from the same day + data from the previous day, so it is not possible to change the formula of the date in the dimension field. I have tried to explain this as simple as possible.

         

      DateActual figuresRequired from set analysis
      1-4-201825-
      2-4-20184025
      3-4-20183540
      4-4-20183035
      5-4-20182030
      6-4-20184520
      7-4-20186045

       

      I have tried the following two formulas:

       

      sum({<[L_BEGIN_DATE.autoCalendar.Date]={'$(=Date(today()-1))'}>}LOAD_INDEX)

      This formula shows only the result of yesterday. So today it's 4th of April, so in the example table Qlik would only show the figure of the 3rd of April, which is 40.

       

      SUM({<[L_BEGIN_DATE.autoCalendar.Date]={'$(=Date(Max([L_BEGIN_DATE.autoCalendar.Date])-1))'}>}LOAD_INDEX)

      This formula shows only the result of the last date that is part of the table -1. So the last date is 7th of April, and Qlik would show the figure of 6th of April, which is 20.

       

      These formulas gave me only the result of 1 date. But i have a table with the data as a dimension, and I need a formula that shows me results of the day before the dimension date on the actual dimension date.

       

      Is there a formula that can do this?