Ignore filter in aggr expression

rasmusnielsen — Thu, 20 Sep 2018 11:27:45 GMT

Hi guys,

I have a KPI in my Qlik sense app with the following measure:

Count(distinct Aggr(nodistinct If(Avg({1<date = {"$(vCompareDate1)"}, brand={"B1"}>} [discount]) > Avg({1<date = {"$(vCompareDate2)"}, brand={"B1"}>} [discount]), [station]), [station], [product]))

My model looks like:

Data
date (date)
station (string)
product (string)
brand (string)
segment (string)
discount (double)

In the app the user can select two dates which are then stored into variables vCompareDate1 and vCompareDate2. The KPI should show the amount of unique stations that has a lower discount on vCompareDate2 compared to vCompareDate1.

I have two filters: segment and product. I would like to make the KPI ignore the segment filter. But I have not been able to do so yet.

I have tried the following measures without any luck:

Count({1}distinct Aggr(nodistinct If(Avg({1<date = {"$(vCompareDate1)"}, brand={"B1"}>} [discount]) > Avg({1<date = {"$(vCompareDate2)"}, brand={"B1"}>} [discount]), [station]), [station], [product]))

Count({<segment=>}distinct Aggr(nodistinct If(Avg({1<date = {"$(vCompareDate1)"}, brand={"B1"}>} [discount]) > Avg({1<date = {"$(vCompareDate2)"}, brand={"B1"}>} [discount]), [station]), [station], [product]))

Count(distinct Aggr(nodistinct If(Avg({1<date = {"$(vCompareDate1)"}, brand={"B1"}, segment=>} [discount]) > Avg({1<date = {"$(vCompareDate2)"}, brand={"B1"}, segment=>} [discount]), [station]), [station], [product]))

Count(distinct Aggr(nodistinct If(Avg({$<segment=>*1<date = {"$(vCompareDate1)"}, brand={"B1"}>} [discount]) > Avg({$<segment=>*1<date = {"$(vCompareDate2)"}, brand={"B1"}>} [discount]), [station]), [station], [product]))

Count({<segment=>}distinct Aggr(nodistinct If(Avg({$<segment=>*1<date = {"$(vCompareDate1)"}, brand={"B1"}>} [discount]) > Avg({$<segment=>*1<date = {"$(vCompareDate2)"}, brand={"B1"}>} [discount]), [station]), [station], [product]))

(I am also using this measure inside a synthetic dimension, which is why I have the distinct / nodistint's, according to http://www.naturalsynergies.com/q-tip-14-aggr-and-synthetic-dimensions/).

Any suggestions?

Thanks!

Re: Ignore filter in aggr expression

rasmusnielsen — Fri, 21 Sep 2018 06:17:11 GMT

I was just wondering if my issue is that I don't have an aggregation function inside my aggr, but just the [station] dimension?

But I can't see what an alternative approach would be?

Re: Ignore filter in aggr expression

rasmusnielsen — Fri, 21 Sep 2018 08:23:04 GMT

Managed to solve the problem after reading:

Use Aggregation Functions!

Wrapped the [station] in the AGGR function with Only({1}station);

topic Re: Ignore filter in aggr expression in App Development

Ignore filter in aggr expression

Re: Ignore filter in aggr expression

Re: Ignore filter in aggr expression