topic Re: Previous month with set expression in QlikView

Previous month with set expression

oehmemat — Sat, 16 Nov 2024 02:02:28 GMT

Hello Community, I have following Table:

Date	Value
28.02.2019	325
31.03.2019	333
30.04.2019	328
31.05.2019	326
30.06.2019	300
31.07.2019	293
31.08.2019	292
30.09.2019	290
31.10.2019	282

No I want to make a bar chart with the difference between each month. Eg:

Date	Diff
28.02.2019
31.03.2019	8
30.04.2019	-5
31.05.2019	-2
30.06.2019	-26
31.07.2019	-7
31.08.2019	-1
30.09.2019	-2
31.10.2019	-8

I can't figure it out how define the set from the month before, something like

{$<Date ={"$(=monthend(addmonths(Date,-1)))"}>}

isn't working as the result aggregates with the Date in the Date column and the set expression returns zero.

Is it even possible with set expression or lies the solution in the script?

Re: Previous month with set expression

zhadrakas — Tue, 05 Nov 2019 10:48:42 GMT

maybe this expression will do it:

sum(Value) - above(sum(Value))

regards

tim

Re: Previous month with set expression

jaibau1993 — Tue, 05 Nov 2019 11:15:42 GMT

Hi! Set analysis is evaluated once per chart. This means that you can't build a "row-wise set analysis" expression but it is what you need: for month (row) you have to calculate the difference with respect to the previous month. Said so, the solution may be similar to the one provided by Zhadrakas

Regards,

Jaime.

Re: Previous month with set expression

oehmemat — Tue, 05 Nov 2019 11:34:21 GMT

Thank you very much, the above(function) did the trick.