topic Cumulative sum expression that works with cyclic group? in QlikView

Cumulative sum expression that works with cyclic group?

joeallen — Sat, 16 Nov 2024 19:54:58 GMT

Hello

Say I have sum(Sales)

I want it as a cumulative sum over a time period, to start with Date

So say sum sales for 20191001 is 200, for 20191002 is 300, then it should show 200+300 as the value on 20191002 etc...

Then you can for example use the new for QV12 sortable AGGR function

Aggr(RangeSum(Above(sum(Sales), 0, RowNo())), YearMonth, (Date, (NUMERIC)))

This works for me.

HOWEVER

My time period is really not only Date, but it's a cyclic group with Date, YearWeek, YearMonth and Year

The above expression only works for Date, but not for the others

How do I make an expression with cumulative sum that works for this cyclic time group?

Re: Cumulative sum expression that works with cyclic group?

tresB — Fri, 11 Oct 2019 11:36:20 GMT

Try replacing your direct field in aggr sort argument by expression: GetCurrentField (group_name), like:

Aggr(RangeSum(Above(sum(Sales), 0, RowNo())), YearMonth, (GetCurrentField (group_name), (NUMERIC)))

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 11:47:11 GMT

Correct me if I am wrong @tresB , but won't we need Dollar sign expansion here?

Aggr( RangeSum(Above(Sum(Sales), 0, RowNo())) , YearMonth, ($(='[' & GetCurrentField (group_name) & ']'), (NUMERIC)))

Re: Cumulative sum expression that works with cyclic group?

tresB — Fri, 11 Oct 2019 12:22:56 GMT

Sunny,

That is something I wanted to test and post. In fact I typed two possibilities as answer, one with $ and another without, then I thought that, since the getcurrentfield() would actually be evaluated with the chart (since it is dimension-selection sensitive) - the main expression and getcurrentfield() evaluation might work simultaneously. Again, not tested here.

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 12:23:09 GMT

From my understanding of using this in the past, GetCurrentField() will just give the name of the field as a text... to actually make it read as a field, you will need $()... but let me know if you find otherwise while testing.

Re: Cumulative sum expression that works with cyclic group?

tresB — Fri, 11 Oct 2019 12:40:53 GMT

If the field name doesn't contain space, that should probably not matter. So it's not about evaluating before the actual expression gets evaluated, rather adding '[' ']' to give a field name convention for those even that has spaces in field name.

Edit: I roughly tested and it works (without space in field name obviously)

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 12:45:03 GMT

Spaces is taken care by '[' and ']', but to convert it to field is done by $(=)... I still feel it is needed.

Re: Cumulative sum expression that works with cyclic group?

tresB — Fri, 11 Oct 2019 12:45:52 GMT

Sunny, so yes, yours is a better approach since that would work in all scenarios. 🙂

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 12:48:28 GMT

For some reason I don't see it working without $().. please check the attached file

Re: Cumulative sum expression that works with cyclic group?

joeallen — Fri, 11 Oct 2019 12:50:04 GMT

Thanks for the idea Tresesco and for the correction Sunny.

It works fine for Date and YearWeek (I want the cumulative sum to reset per YearMonth for Date and YearWeek so I want this expression as is for those two)

However once the Cyclic group changes to YearMonth it doesn't work any more (it just sums like normal and not cumulative per YearMonth, I assume because of the YearMonth grouping in the expression that works so well for YearWeek and Date. And Year doesn't work at all.
I guess have to use some kind of If function here to get YearMonth and Year to work?

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 12:59:17 GMT

Yes, some sort of if statement would be needed to get this to work.

Re: Cumulative sum expression that works with cyclic group?

tresB — Fri, 11 Oct 2019 13:02:09 GMT

Sunny,

I tried expression: =Avg(F3)& Count(GetCurrentField([Group]))

and it works, I guess it's about scope where/when you are using this. Today I have to log off early, but definitely will come back and do some more testing..

Re: Cumulative sum expression that works with cyclic group?

joeallen — Fri, 11 Oct 2019 13:02:33 GMT

So simply an If statement when GetCurrentField is Date or YearWeek, then have the expression as YearMonth grouping, and if GetCurrentField is YearMonth, have Year as grouping in the expression, for example?

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 13:07:35 GMT

I don't think that this is working as you might be expecting. =Avg(F3)& Count(GetCurrentField([Group])). All it is doing is to Counting the text... not the field itself.... Try this =Avg(F3)& Concat(DISTINCT GetCurrentField([Group])) and you will see F2 instead of AA and BB when you are on F2 within your group.

Re: Cumulative sum expression that works with cyclic group?

sunny_talwar — Fri, 11 Oct 2019 13:08:50 GMT

I think so, exact expression will have to tested out but you can have an overarching if statement or you can have an if statement just for your dimensions part.... but your idea is right

Re: Cumulative sum expression that works with cyclic group?

tresB — Tue, 15 Oct 2019 08:58:47 GMT

Spot on Sunny. Thanks.