Skip to main content

Qlik Community

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
irmantas
Contributor III
Contributor III
‎2021-11-24 09:02 AM

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

190 Views
0 Likes
2 Replies
irmantas
Contributor III
Contributor III
‎2021-11-25 09:22 AM
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.

129 Views
0 Likes