How to not filter on aggr dimensions in if statement?

Femke_DPG — Mon, 23 May 2022 07:00:25 GMT

Is there a way to not filter on the dimensions, used in the aggregation sum in an if-statement?

Example:

if([Sales type group]='A'
and aggr(sum(if([Media Name]='F' ,[Sales])),[Clean Product Name],Month,Year)<>0
and aggr(sum(if([Media Name]='G' ,[Sales])),[Clean Product Name],Month,Year)<>0
and aggr(sum(if([Media Name]='E' ,[Sales])),[Clean Product Name],Month,Year)<>0
and aggr(sum(if([Media Name]='A' ,[Sales])),[Clean Product Name],Month,Year)<>0
and aggr(sum(if([Media Name]='T' ,[Sales])),[Clean Product Name],Month,Year)<>0 ,'MIX' ,

if([Sales type group]='A'
and aggr(sum(if([Media Name]='F' ,[Sales])),[ Clean Product Name],Month,Year)=0
and aggr(sum(if([Media Name]='G' ,[Sales])),[Clean Product Name],Month,Year)<0
and aggr(sum(if([Media Name]='E' ,[Sales])),[Clean Product Name],Month,Year)>0
and aggr(sum(if([Media Name]='A' ,[Sales])),[Clean Product Name],Month,Year)>0
and aggr(sum(if([Media Name]='T' ,[Sales])),[Clean Product Name],Month,Year)>0 ,'MIX2' ))

When I filter on 'MIX'or MIX2, there are other filters that are applied above:

I think it filters on the values that are in the aggr statement.: year, month, product name (en sales type group is also in the if-statement).

It only needs to show 'MIX', but it shows nearly everything, so also other mixes.

Many thanks for the help!

Appreciate your help!

Thanks!

Re: How to not filter on aggr dimensions in if statement?

vinieme12 — Mon, 23 May 2022 08:16:20 GMT

@Femke_DPG wrote:

It only needs to show 'MIX', but it shows nearly everything, so also other mixes.

Since this is a calculated field created in the front end it doesn't exist in the data model as an actual "FIELD"

but is basically a combination of different field value filters

There is NO workaround for this, QlikSense will always show filters applied on fields that exist in datamodel and not calculated fields

topic Re: How to not filter on aggr dimensions in if statement? in App Development

How to not filter on aggr dimensions in if statement?

Re: How to not filter on aggr dimensions in if statement?