Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner
Partner

Ignore filter in aggr expression

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!

Tags (1)
2 Replies
Highlighted
Partner
Partner

Re: Ignore filter in aggr expression

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?

Highlighted
Partner
Partner

Re: Ignore filter in aggr expression

Managed to solve the problem after reading:

Use Aggregation Functions!

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