topic Re: Ignore selections in calculated dimension? in QlikView

Ignore selections in calculated dimension?

joeallen — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Ignore selections in calculated dimension?

andrey_krylov — Mon, 29 Oct 2018 12:28:10 GMT

Maybe to use Today() instead of max(Date)

=IF(Aggr(Rank(SUM({<DAYDIFF={">$(=$(DelivCreationDays))"},Date = {'$(=Today())'}>}ValueEUR)),Country)<=5,Country)

Re: Ignore selections in calculated dimension?

sunny_talwar — Mon, 29 Oct 2018 12:31:37 GMT

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)

Re: Ignore selections in calculated dimension?

jaibau1993 — Mon, 29 Oct 2018 12:34:35 GMT

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.

Re: Ignore selections in calculated dimension?

joeallen — Mon, 29 Oct 2018 12:51:29 GMT

Thanks everyone for the swift assistance! All solutions worked, If I could I would mark all answers as correct.

Re: Ignore selections in calculated dimension?

StacyCui — Fri, 02 Dec 2022 13:43:48 GMT

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?