Ignore a Filter Selection in a Aggregate Function with Set Analysis

[RMMFCA]

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.

[marcus_malinow]

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]

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]

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

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

[RMMFCA]

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]

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]

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

[sunny_talwar · Accepted solution]

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]

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

[Sudhakar1523]

Hi @sunny_talwar 

Can you please help me to write  ignore filter selection for below logic.

I tried using above reply but in aggr it is showing 0 value when making selection in fiscal month filter.

sum(
fabs(
Aggr(
sum({<"Snapshot Fiscal Period Year"=, "Fiscal Month"=, Mape_MonthYear={"$(=Date(Addmonths(Date(Max(Mape_MonthYear),'YYYYMM'),-1),'YYYYMM'))"}>}[Forecast 60])
-
sum({<"Snapshot Fiscal Period Year"=, "Fiscal Month"=, Mape_MonthYear={"$(=Date(Addmonths(Date(Max(Mape_MonthYear),'YYYYMM'),-1),'YYYYMM'))"}>}
SO_Mfg_Usage_Primary_Shipped_Quantity)
,[Account Name],FiscalPeriod_Key1)
)
)/
sum({<"Snapshot Fiscal Period Year"=, "Fiscal Month"=, Mape_MonthYear={"$(=Date(Addmonths(Date(Max(Mape_MonthYear),'YYYYMM'),-1),'YYYYMM'))"}>}
SO_Mfg_Usage_Primary_Shipped_Quantity)