Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to show Last Value on pivot insted of Expression

Hi guys

I'm having a problem with expand/collapse

I have a pivot table, with month/week as dimensions.

I want to show the last week value, instead of the expression by itself, when i choose to collapse it

Is there any way to to that?

Example:

Month (collapsed)

50.000

Month/Week (expanded)

20.000 / 10.000 / 10.000 / 10.000

I want to show 10.000 instead of 50.000 when i collapse [the explression is a sum()]

The real number is "Stock Cost". That beeing said, when i choose to collapse, i dont want to see the sum, i want to see how the month ended

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

May be this:

=FirstSortedValue(Aggr(Sum(Value), Month, Week), -Aggr(Week, Week, Month))


Capture.PNG

View solution in original post

4 Replies
Gysbert_Wassenaar

Try min(aggr(sum([Stock Cost]) , DimA, DimB, ... , DimX)). Replace DimA, DimB, ... , DimX with the dimensions of the pivot table.


talk is cheap, supply exceeds demand
sunny_talwar

May be this:

=FirstSortedValue(Aggr(Sum(Value), Month, Week), -Aggr(Week, Week, Month))


Capture.PNG

Not applicable
Author

Ty gwassenaar‌ and stalwar1

it workd exactly as i wanted!

berkarmagan
Partner - Creator
Partner - Creator

Hi, can you look over this if u can Pivot Table total func problem‌ , hope to not bother you. Thanks.