Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
the dimension will return the Date if it is equal to the maximum date else null, so set suppress when null
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 ?
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.
welcome btw