topic Re: Set Analysis in Aggregate in QlikView

Set Analysis in Aggregate

aointl_jelee — Thu, 08 Mar 2018 17:15:15 GMT

Hi all,

I am using set analysis to ignore the selections made on a year field, however this is not resolving properly as when I make a selection on year the expression is filtered.

Has anyone had experience when ignoring selections on a field using an aggregate equation? The year is used as part of key for KeyValue1,2,3.

=NUM(
SUM({$ YEARFIELD= >}AGGR(

   (
      SUM({$ YEARFIELD= >}AGGR(
     [Material]
      , KeyValue2))
   )

*
      SUM({<KeyValue-={''}, YEARFIELD =>}AGGR(
      ([Area])
      ,KeyValue3))

,KeyValue1))
, '$#,##0.00')

Re: Set Analysis in Aggregate

Digvijay_Singh — Thu, 08 Mar 2018 17:49:30 GMT

Your expression looks confusing, Material and Area are not surrounded by any aggregate function, you may try to surround them with Only like this if you feel your logic is working fine -

Only({<YEARFIELD =>}[Area])

Re: Set Analysis in Aggregate

isingh30 — Thu, 08 Mar 2018 17:52:18 GMT

Try like this - Sample

Using 1 will ignore all selections:

sum({1<Category = {'category1'}>}[Number of people])

If you only want to ignore selections in a particular field, you would do it this way:

sum({$<Category = {'category1'}, IgnoreField =>}[Number of people])

Re: Set Analysis in Aggregate

aointl_jelee — Thu, 08 Mar 2018 17:52:58 GMT

Material and Area both are within an aggregate function.

=NUM(
SUM({$ YEARFIELD= >}AGGR(

SUM({$ YEARFIELD= >}AGGR([Material], KeyValue2))

SUM({<KeyValue-={''}, YEARFIELD =>}AGGR([Area],KeyValue3))

,KeyValue1))
, '$#,##0.00')