Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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
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