-1

I have a series of dates, eg.:

20160628
20160627
20160623
20160620

If I select a particular date, I want to find the date immediately prior to that date.

For example:

  • If I select 20160628, I get 20160627.
  • If I select 20160627, I get 20160623.
  • If I select 20160623, I get 20160620.

Is there a way to do this in QlikView?

PingPing
  • 899
  • 6
  • 15
  • 28
  • Where do you want to "get" it? In a text object? – mickeger Jun 28 '16 at 08:17
  • The easiest way to do this is in your load script and +1 to your date! This will move everything on one day. However it entirely depends on what you are trying to achieve, are you trying to change the date shown, pick the date in a chart? Some more info is required before an answer can be given. – Chris J Jun 28 '16 at 13:45

1 Answers1

0

I'm expecting that you'll have more than one row of data with each date, so I'll use this INLINE data as an example :

   BASETABLE:
   LOAD * INLINE [
       myDate,myType
       20160628,A
       20160628,B    
       20160627,C
       20160627,D
       20160627,E    
       20160623,F
       20160623,G
       20160623,H        
       20160620,I
       ];

Now we'll start by creating a very dumb temporary table with all the unique dates that are available :

    UNIQUE_DATE:
    LOAD Distinct myDate Resident BASETABLE order by myDate;

We can now, create a new table that will contain all of the existing dates and the correspondent last date :

    PREVIOUS_DATE:
    LOAD myDate, Previous(myDate) as myPrevDate Resident UNIQUE_DATE order by myDate;

The trick is the command Previous and the fact that we are ordering the table by myDate. Finally you can drop the UNIQUE_TABLE, it won't be needed any more :

    DROP TABLE UNIQUE_DATE;

You'll end up with the field myDate (the original one) and the myPrevDate, which contains what (I think) you are expecting.

BrunoMarques
  • 557
  • 2
  • 11