Pivot table - Challenge

[priya945]

Hi All, I have a Pivot table in QV as below when expanded 

	East	West
Aug-19	Sep-19	Oct-19	Aug-19	Sep-19	Oct-19
A	1	3	2	3	5	4
B	4	5	7	8	9	11
C	2	4	3	4	5	5

When user collapse, Pivot table looks like below where dates are not showing and values are summing up. 

	East	West
A	6	12
B	16	28
C	9	14

Requirement is When user collapse then the last date with values should be shown instead of blank date and sum up of all as below

	East	West
Oct-19	Oct-19
A	2	4
B	7	11
C	3	5

Please advise if this can be achieved in pivot table

Thank You

[sunny_talwar · Accepted solution]

You can get the latest month's value, but not sure how you would display the month itself

=If(SecondaryDimensionality() = 2, Sum(Value), FirstSortedValue(Aggr(Sum(Value), Field, Region, Date), -Aggr(Date, Field, Region, Date)))

 

[priya945]

Thanks Sunny.

Yes got the solution partially and the challenge would be date.

Anyways thanks once again.