Quarter Before

[SonPhan]

Hello Qlik Experts,

I have a question, what is the best way to move key figures in a quarter back by one quarter?

INPUT DATA:
LOAD * INLINE [
FORECAST_DATE_Year_QUARTER, Measure.
2019 Q1, 100
2019 Q2, 200
2019 Q2, 300
2019 Q3, 300
2019 Q4, 400
2020 Q1, 500
];

Result should looks like this:

FORECAST_DATE_Year_QUARTER	FORECAST_DATE_Year_QUARTER_BEFORE	Measure	Measure_BEFORE
2019 Q1	2018 Q4	100
2019 Q2	2019 Q1	200	100
2019 Q2	2019 Q1	300	200
2019 Q3	2019 Q2	300	300
2019 Q4	2019 Q3	400	300
2020 Q1	2019 Q1	500	400

The background why I would like to program it this way, is because I want to avoid complex Set Analysis on the frontend.

I hope I was able to explain my problem.

Best regards Son

[Vegar · Accepted solution]

Try this:

[INPUT DATA]:
LOAD
   FORECAST_DATE_Year_QUARTER,
   previous(FORECAST_DATE_Year_QUARTER) as FORECAST_DATE_Year_QUARTER_BEFORE,
   Measure,
   previous(Measure) as Measure_BEFORE
INLINE [
   FORECAST_DATE_Year_QUARTER, Measure
   2018 Q4,
   2019 Q1, 100
   2019 Q2, 200
   2019 Q2, 300
   2019 Q3, 300
   2019 Q4, 400
   2020 Q1, 500
]
Where
   NOT isNull(previous(FORECAST_DATE_Year_QUARTER))
;