Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
RMMFCA
New Contributor II

Ignore a Filter Selection in a Aggregate Function with Set Analysis

Hello,

I've been trying to figure out how to ignore a selected filter/dimension within a set analysis expression below.

SUM({$< sales_code=, brand = {'Farm'} >} AGGR(DISTINCT q_cnt, c_number, n_seg, n_mod, c_series, c_centr))

This expression works fine without sales_code=, but I need to ignore the value that is selected in the sale code field. What am I missing here? Any help would be appreciated.

Tags (2)
Labels (2)
1 Solution

Accepted Solutions
Highlighted

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

May be this

Sum({<sales_code, brand = {'Farm'}>} AGGR(
    Only({<sales_code, brand = {'Farm'}>} q_cnt)
, c_number, n_seg, n_mod, c_series, c_centr))
8 Replies
marcus_malinow
Valued Contributor III

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

I'm not sure what you're trying to accomplish here, but there is an obvious issue with your AGGR (which may well not be necessary anyway)

AGGR is performed on another aggregation function such as SUM or COUNT. You shouldn't be performing a SUM on an AGGR which is itself not being performed on an aggregation.

What field are you trying to SUM here, and what fields are you trying to manipulate with your set analysis?

RMMFCA
New Contributor II

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

I am trying to SUM the column q_cnt for every grouping of c_number, n_seg, n_mod, c_series, c_centr where the brand is Farm while the sales_code selected filter value is ignored.

marcus_malinow
Valued Contributor III

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

Ok, so is there a particular reason you didn't try this?

Sum({$<brand={'Farm'}, sales_code=>} q_cnt)

 

RMMFCA
New Contributor II

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

I did. It gives me the wrong answer. Plus, your solution doesn't group or look at the other columns that I previously stated into consideration with the q_cnt.

marcus_malinow
Valued Contributor III

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

Ok, to be honest it sounds like a simple requirement that really shouldn't require an AGGR. If this were my data model I'd be looking into whether there were any issues with it.

Can you upload a (small) sample of your data that can illustrate the issue, and indicate the required result?

 

marcus_malinow
Valued Contributor III

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

Re grouping by the fields c_number, n_seg, n_mod, c_series, c_centr

Given that you're just doing a SUM, this *shouldn't* be necessary. If, for example, you were looking for the MIN or MAX across the different combinations of these fields then fair enough, an AGGR would probably be required, but not for a SUM

 

Highlighted

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

May be this

Sum({<sales_code, brand = {'Farm'}>} AGGR(
    Only({<sales_code, brand = {'Farm'}>} q_cnt)
, c_number, n_seg, n_mod, c_series, c_centr))
RMMFCA
New Contributor II

Re: Ignore a Filter Selection in a Aggregate Function with Set Analysis

Genius!! How did I miss this combination?!?!