topic Re: RangeSum by multiple dimensions in QlikView

RangeSum by multiple dimensions

tschullo — Sat, 16 Nov 2024 02:53:42 GMT

I am using QV12 and need to solve what seems like a simple problem:

Make	Model	MonYr	Sales	3MonSalesCnt
Ford	Pinto	Dec 2019	0	3
Ford	Pinto	Nov 2019	2	3
Ford	Pinto	Oct 2019	1	1
Ford	Escort	Dec 2019	1	2
Ford	Escort	Nov 2019	1	1
Ford	Escort	Oct 2019	0	0

I need a way to calculate 3MonSaleCnt (running count of sales for past 3 months)
I can't rely on the number of months always being >= 3 and the method must respect a change in Make and/or Model.
I am open to a script OR expression solution (personally, I prefer a script solution for dashboard efficiency's sake)

Re: RangeSum by multiple dimensions

Frank_Hartmann — Sat, 27 Jul 2019 12:02:27 GMT

Maybe try like this:

tmp: Load *, (RowNo()) as Row; Load * Inline [ Make, Model, MonYr_, Sales Ford, Pinto, Dec 2019, 0 Ford, Pinto, Nov 2019, 2 Ford, Pinto, Oct 2019, 1 Ford, Escort, Dec 2019, 1 Ford, Escort, Nov 2019, 1 Ford, Escort, Oct 2019, 0 ]; NoConcatenate Load *,RangeSum(Sales, If(Model = Peek(Model, -1), Peek(Sales,-1)),If(Model = Peek(Model, -2), peek(Sales,-2))) as [3Month] Resident tmp Order by Row desc; DROP Table tmp;

Re: RangeSum by multiple dimensions

tschullo — Mon, 29 Jul 2019 12:58:03 GMT

Thank you Frank!

Simple yet elegant.

In my data there is a chance that the second dimension exists for multiple "Make" values, so I adjusted:

Load *,
RangeSum(Sales, If(Model = Peek(Model, -1) And Make = Peek(Make, -1), Peek(Sales,-1)),
If(Model = Peek(Model, -2) And Make = Peek(Make, -2), peek(Sales,-2))
) as [3Month]
Resident tmp Order by Row desc;