# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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
Specialist III

create a calculated dimension:

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

=sum(VALUE)
5 Replies
Specialist III

create a calculated dimension:

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

=sum(VALUE)
Specialist III

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 ?

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.

Specialist III

welcome btw

Community Browser