topic Sum aggregation up to the date in period in App Development

Sum aggregation up to the date in period

irmantas — Wed, 24 Nov 2021 14:02:01 GMT

Hello,

I have situation when loading Items transaction list and representing data in Pivot table, were columns meaning week dimension (From master calendar) and I need to calculate total sums per Item were transaction date "<" last date of current week.

How to use sum formula, to calculate all transactions out of the selected weeks in dimensions?

I was traying this: Sum (if(TransDate<(Max(TransDate)),QTY)) but do not works. In my meaning Max(TransDate) means last transaction date in current week 🙂

Please help

Re: Sum aggregation up to the date in period

MarcoWedel — Wed, 24 Nov 2021 19:59:05 GMT

maybe helpful:

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/NestedAggregations/nested-aggregations.htm

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm#:~:text=the%20Aggr()%20function%20can%20be%20compared%20to%20creating%20a%20temporary%20staged%20result%20set%20(a%20virtual%20table)%2C%20over%20which%20another%20aggregation%20can%20be%20made.

Re: Sum aggregation up to the date in period

irmantas — Thu, 25 Nov 2021 14:22:09 GMT

I will try explain graphically 🙂

I have simple data:

TransDate	ItemId	QTY
2021-11-04	10001	56
2021-11-05	10001	20
2021-11-09	10001	13
2021-11-10	10001	25
2021-11-12	10001	45
2021-11-16	10001	5
2021-11-19	10001	33
2021-11-23	10001	13
2021-11-25	10001	20

Need make report - pivot table per Items and weeks:

Pivot table
ItemId	Week 44	Week 45	Week 46	Week 47
10001	76	159	197	230

Sum must accumulate all transactions per ItemId, and show total situation an current week.