Displaying the max value of a dimension in pivot table while not impacting expression

[aetingu12]

Dear all,

I have attached an example of what I am trying to do.  Basically, I would like to pick up the max date in my dimension within a pivot table to display, but don't want to change the expression totals.

ie.

PRODUCT	DATE	Amount
A	31/01/2019	100
A	28/02/2019	200

I would like my pivot to be displayed as:

PRODUCT	DATE	Amount
A	28/02/2019	300

Help is much appreciated.

[tresesco]

Instead of using DATE as dimension, use first expression as : Date(Max(DATE))

[Uday_Pasupuleti]

Try this

[aetingu12]

Thank you for coming back to me.

What do you mean by use first expression?  You mean create a calculated dimension or use set analysis in my expression?

Regards,

[aetingu12]

Hi Uday,

I use Qlikview, for some reason when I create a calculated dimension, Date(max(Date)); it is giving me a calculation error, even thought there are no errors.

[aetingu12]

This is what I am getting:

[Uday_Pasupuleti · Accepted solution]

Hi,

Use as a measure.

[aetingu12]

Happy Days, thank you.  It would be nice to see if there is a way to do it within the dimension as well.

[Uday_Pasupuleti]

In that case, you have to make some changes at script level.

Try this

Tab:
Load * Inline
[Product,Date,Amount
A,31/01/2019,100
A,28/02/2019,200];

Test:
NoConcatenate
Load
Product,
Date(Max(Date)) as NewDate
Resident Tab
Group by Product;