Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
irmantas
Contributor III
Contributor III

Sum aggregation up to the date in period

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

2 Replies
irmantas
Contributor III
Contributor III
Author

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.