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: 
aetingu12
Creator
Creator

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

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.

PRODUCTDATEAmount
A31/01/2019100
A28/02/2019200

I would like my pivot to be displayed as:

PRODUCTDATEAmount
A28/02/2019300

 

Help is much appreciated.

 

Labels (1)
1 Solution

Accepted Solutions
Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Hi,

 

Use as a measure.

 

View solution in original post

8 Replies
tresesco
MVP
MVP

Instead of using DATE as dimension, use first expression as : Date(Max(DATE))
Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Try this

Capture1.PNG

aetingu12
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

This is what I am getting:

Capture.PNG

Uday_Pasupuleti
Partner - Creator III
Partner - Creator III

Hi,

 

Use as a measure.

 

aetingu12
Creator
Creator
Author

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
Partner - Creator III
Partner - Creator III

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;

 

Capture2.PNG