aggr overrides set analysis

[p_verkooijen]

I've got a aggregation which display's what location/week combinaties are valid to compare.

However when I select a location only these locations remain in the output. I can't apply Set Analysis on the AGGR.

I want all locations because of benchmark purposes.

see attachtment

[Gysbert_Wassenaar · Accepted solution]

Try:

=CONCAT({1}DISTINCT
  AGGR(
  IF(
  (
  FABS(
  (SUM({1<Sales_Year={2013}>} total <Location, Sales_Week> SalesRow_Total_Realized) - SUM({1<Sales_Year={2012}>} total <Location, Sales_Week> SalesRow_Total_Realized))
  / SUM({1<Sales_Year={2012}>} total <Location, Sales_Week> SalesRow_Total_Realized)
  )
  <= 0.75
  )
  , only({1}Location&'|'&Sales_Week)
  )
  ,  Location, Sales_Week)
, CHR(10) )

[p_verkooijen]

Hi Gysbert,

Thanks, I did find the ONLY({1} in some discussions but couldn't get it to work.

Can you explain the use of ONLY ?

[Gysbert_Wassenaar]

The only function is the standard aggregation function. It returns a value when there is only one value, otherwise it returns null. In your if statement you had such an implicit use of the only aggregation function: Location&'|'&Sales_Week. I made it explicit and added the set modifier {1} to it to get the result you wanted.