Ignore filter in aggr expression

[rasmusnielsen]

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!

[rasmusnielsen]

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?

[rasmusnielsen]

Managed to solve the problem after reading:

Use Aggregation Functions!

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