Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
JM
Contributor III
Contributor III

Set Ananlysis to calculate start period date

I have a data set which includes, three columns, as shown in below table:

LOAD * INLINE [
Period, YearMonth, PeriodEndDate
Apr 2017, 201704, 29/04/2017
Mar 2017, 201703, 01/04/2017
Feb 2017, 201702, 04/03/2017
Jan 2017, 201701, 04/02/2017
Dec 2016, 201612, 31/12/2016
Nov 2016, 201611, 03/12/2016
Oct 2016, 201610, 05/11/2016
Sep 2016, 201609, 01/10/2016
Aug 2016, 201608, 03/09/2016
Jul 2016, 201607, 06/08/2016 
]
;



I only got PeriodEndDate in the table based on this date I want to calculate periodStartDate in a Chart. I can't edit script or model I only have to calculate it using Set Analysis. Any suggestions?

Thanks,

3 Replies
Gysbert_Wassenaar

No idea. You haven't mentioned how is determined when a period starts.

Perhaps this way:

Data:

LOAD * INLINE [

    Period, YearMonth, PeriodEndDate

    Apr 2017, 201704, 29/04/2017

    Mar 2017, 201703, 01/04/2017

    Feb 2017, 201702, 04/03/2017

    Jan 2017, 201701, 04/02/2017

    Dec 2016, 201612, 31/12/2016

    Nov 2016, 201611, 03/12/2016

    Oct 2016, 201610, 05/11/2016

    Sep 2016, 201609, 01/10/2016

    Aug 2016, 201608, 03/09/2016

    Jul 2016, 201607, 06/08/2016  

];

Result:

LOAD

  *,

  Date(Previous(PeriodEndDate)+1) as PeriodStartDate

RESIDENT

  Data

ORDER BY

  PeriodEndDate asc

  ;

DROP TABLE Data;


talk is cheap, supply exceeds demand
JM
Contributor III
Contributor III
Author

you are calculating it correctly, PeriodEndDate + 1 of previous period should be the PeriodStartDate, but I can't do it in the script, I have to do it using Set Analysis.

Thanks

J.M

Gysbert_Wassenaar

I doubt you can do it with set analysis. The sets are calculated at the chart level, not the row level. But perhaps you can achieve something with the above() or below() functions


talk is cheap, supply exceeds demand