1 Reply Latest reply: Jun 27, 2013 3:16 PM by Speros Kokenes RSS

    How to show yesterday's data by selecting today?



      I'm trying to show a comparison between a previous data  and today's data in qv. However I'm stuck because I don't know what to do so qlikview can show me today and yesterdays information by selecting today.


      Let me explain you, I have data everyday for the last 3 years, and I neeed to see everyday how was my number 2 days ago and how is it today, in order to see if I improved or not from what happened 2 days ago.  Therefore I want to select in qv today, and to show me in one column today's information and in another 2 days ago information. I want it to show me 2 days ago information always. No matter if I select 2 months, I want to know the number of those 2 months in one column and in the other the information of 2 days ago.


      Does anyone know how to do it?

        • Re: How to show yesterday's data by selecting today?
          Speros Kokenes

          You can use set analysis to accomplish this. What you will need to know is what "today" constitutes to determine yesterday.


          Does "today" mean the actual current day, regardless of the data model? In that case, you could create a variable for yesterday like so:


          vYesterday: =today()-1


          This will be in numeric format. If you need it as a date string, you can wrap it in date():


          vYesterday: =date(today()-1)


          Alternatively, "today" might just mean the most recent date available in your data or subset of data selected. To calculate this, you will need to take the max of your date field:


          vYesterday: =max([DateField])-1


          vYesterday: =date(max([DateField])-1)


          Now that you have determined the correct day and stored it in a variable, you can use set analysis in the expression you want to limit to yesterday. The expression would look like this:


          sum({<[DateField]={'$(vYesterday)'}>} Expression1)


          What this does it tell QlikView to calculate the sum of Expression1 where your DateField is equal to the value stored in vYesterday.