Sum aggregation up to the date in period

[irmantas]

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

[MarcoWedel]

maybe helpful:

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Neste...

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.....

[irmantas]

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.