Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
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
Master II

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

create a calculated dimension:

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

``=sum(VALUE)``
5 Replies
Master II

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

create a calculated dimension:

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

``=sum(VALUE)``
Master II

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

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 ?

Master II

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.

Master II

welcome btw

Community Browser