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