Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Touix
Contributor III
Contributor III

Cross table - Show only the value for the last date by month

Hello Everyone,

 

I have this data

MONTH,DATE, value

APRIL,15-apr-2020,20

APRIL,16-apr-2020,30

APRIL,16-apr-2020,50

MAY,01-may-2020,10

I want to do a cross table to have the value of the last date per month.

Example :

APRIL,MAY

80,10

I tried:

Sum(
{Date={"$(=Max(Date))"}
VALUE
)

But of course i don't have this value by month, only the max date 😞.  In my table I have only 

MAY

10

In a cross table , how to have the max date value BY MONTH please ?

Thank you for your help and time

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

test the max date in your dimension instead of the expression:

create a calculated dimension:  

=if(date(aggr(nodistinct max(Date),Month)) = Date, Date)

in your expression

=sum(VALUE)

View solution in original post

5 Replies
edwin
Specialist III
Specialist III

test the max date in your dimension instead of the expression:

create a calculated dimension:  

=if(date(aggr(nodistinct max(Date),Month)) = Date, Date)

in your expression

=sum(VALUE)

View solution in original post

edwin
Specialist III
Specialist III

the dimension will return the Date if it is equal to the maximum date else null, so set suppress when null

Touix
Contributor III
Contributor III
Author

It works perfectly.

Thank you very very much for your help.

So I try to understand.

In my dimension, I choose only the last day of each month by  the aggr

And It picks the date that is equal to one of these dates.

Right ?

 

edwin
Specialist III
Specialist III

yes that is correct, it will cycle through all the dates and any date that is equal to any of the aggregation results will have a value, others will have null.  that is why you need to set suppression on the nulls.

edwin
Specialist III
Specialist III

welcome btw