Qlik Community

Ask a Question

App Development

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

April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
Showing results for 
Search instead for 
Did you mean: 

Pivot: Sum the amount per item of a max date based on selected time period

Hey guys. This is a follow up from my previous question.  I'm using the following expression to display the amount of certain items in a pivot tabe over a given period of time.

SUM(AGGR(FirstSortedValue(Amount ,-(Date - (Amount/1E10))), Item, Company))

Now for a single year, I get correct values. But if drill down on quarter, month or day obviously I can't expect a correct value because I didn't define the time period in my Aggr() expression. 

If I add Date to the aggregation I get the reversed result, meaning I can see individual amounts for every day but then they are all summed up when I return to an annual overiew. 

What I like to see however is the amount of each item for the last day based on the selected time period. So for example in 2019 the pivot should return the amount of each item based on max date, which are then summed up by dimension 1. Further drilldowns should follow the same principle resulting in sums based on the max date of the selected drilldown.

See pictures attached for better understanding.

Thank you for you help.

Pivot1: Year Overview


Pivot2: Year expanded






0 Replies