Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following expression:
=Avg(Aggr(Sum({<DAYDIFF={">$(=$(DelivCreationDays))"} >} ValueEUR), Date, Country))
Which gives me the average daily (date) summarized value per country in my graph.
Based on above expression I want to see the top 5 countries for the max(date) (i.e yesterdays date)
So I did a calculated dimension with just a sum() since I only look at a date value, which seem to work with no filters:
=IF(Aggr(Rank(SUM({<DAYDIFF={">$(=$(DelivCreationDays))"},Date = {'$(=max(Date))'}>}ValueEUR)),Country)<=5,Country)
Which gives me TR, IN, TH, UA, ET as result for date = 20181028. Which is correct.
However, I now need this to ignore all time selections in the calculated dimension e.g. Date, YearWeek, YearMonth because I always want to show above countries as top 5 countries no matter what period I look at. So if I select for example filter YearMonth=201801 it should show the January values for the countries above and thus have the calculated dimension still look at 20181028, and NOT look at max(date) = 20180131, which gives me other countries (EG, AR, IN, AE , IR).
I tried using Date=, YearWeek=, YearMonth= in above expression:
=IF(Aggr(Rank(SUM({<DAYDIFF={">$(=$(DelivCreationDays))"}, YearMonth=, YearWeek=, Date=, Date = {'$(=max(Date))'}>}ValueEUR)),Country)<=5,Country)
but it doesn't work, it still gives me the top 5 countries of 20180131 if I select YearMonth=201801. Also not sure how Date= interacts when I also have Date = max (date) in same expression.
Does somebody know what I do wrong and how to solve it?
Hi Joe!
When you select a date, max(Date) changes (it is no longer "yesterday"). Thus, the set statement
Date = {'$(=max(Date))'}
in your set analysis is not working properly. You have to use
Date = {'$(=max({1} Date))'}
Or, if you prefer, just replace your calculated dimension by:
=IF(
Aggr(
Rank(
SUM({<DAYDIFF={">$(=$(DelivCreationDays))"}, YearMonth=, YearWeek=, Date=, Date = {'$(=max({1} Date))'}>} ValueEUR)
)
,Country)
<=5, Country)
Bests,
Jaime.
Maybe to use Today() instead of max(Date)
=IF(Aggr(Rank(SUM({<DAYDIFF={">$(=$(DelivCreationDays))"},Date = {'$(=Today())'}>}ValueEUR)),Country)<=5,Country)
Another option is to ignore selection for Max(Date) using {1}
=If(Aggr(Rank(Sum({<DAYDIFF = {">$(=$(DelivCreationDays))"}, YearMonth, YearWeek, Date = {'$(=Max({1}Date))'}>} ValueEUR)), Country) <= 5, Country)
Hi Joe!
When you select a date, max(Date) changes (it is no longer "yesterday"). Thus, the set statement
Date = {'$(=max(Date))'}
in your set analysis is not working properly. You have to use
Date = {'$(=max({1} Date))'}
Or, if you prefer, just replace your calculated dimension by:
=IF(
Aggr(
Rank(
SUM({<DAYDIFF={">$(=$(DelivCreationDays))"}, YearMonth=, YearWeek=, Date=, Date = {'$(=max({1} Date))'}>} ValueEUR)
)
,Country)
<=5, Country)
Bests,
Jaime.
Thanks everyone for the swift assistance! All solutions worked, If I could I would mark all answers as correct.
Hi, recently , I met a same question. I need create a new dimension that 'OR Growth' and 'OR decrease' till now based on measure. The first pic is dimension, and for OR type is CY till OR- PY till OR which is the second pic. When I select month, it's not show all the customers instead of customers have OR value till that month. How to solve it?