topic Re: aggr overrides set analysis in QlikView

aggr overrides set analysis

p_verkooijen — Mon, 11 Nov 2013 14:08:03 GMT

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

Re: aggr overrides set analysis

Gysbert_Wassenaar — Mon, 11 Nov 2013 14:36:43 GMT

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) )

Re: aggr overrides set analysis

p_verkooijen — Mon, 11 Nov 2013 14:47:27 GMT

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 ?

Re: aggr overrides set analysis

Gysbert_Wassenaar — Mon, 11 Nov 2013 14:55:51 GMT

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.