topic AGGR + Set analysis with the same DIM in QlikView

AGGR + Set analysis with the same DIM

karolina_ — Wed, 13 Dec 2017 10:36:04 GMT

Hi All,

I have a problem w AGGR with Set analysis the there is the same DIM in both

I want to compare in chart current year to previosu year and I have the expressions:

[This Year]

sum({<Year={$(=if(GetSelectedCount(Year)=1,Only(Year),vCurrentYear))}>} aggr(Count(DISTINCT

if([Date2]>=MonthStart and [Date2]<=MonthEnd, ID)

),ID, Year, Month, Date2))

[Year Ago]

sum({<Year={$(=if(GetSelectedCount(Year)=1,Only(Year),vCurrentYear)-1)}>} aggr(Count(DISTINCT

if([Date2]>=MonthStart and [Date2]<=MonthEnd, ID)

),ID, Year, Month, Date2))

This is working fine when no Year is selected.

When Year is selected (even the same as vCurrentYear) second expression [Year Ago] is not working.

I noticed that if I remove Year from AGGR Dimensions it is showing results whether Year is selected or not, however values are different with and without Year as AGGR Dim

sum({<Year={$(=if(GetSelectedCount(Year)=1,Only(Year),vCurrentYear)-1)}>} aggr(Count(DISTINCT

if([Date2]>=MonthStart and [Date2]<=MonthEnd, ID)

),ID, Month, Date2))

I have tried putting set analysis at different places but still they were not working

sum({SET ANALYSIS} aggr ( count (
sum(aggr ( count ({SET ANALYSIS}
sum(aggr {SET ANALYSIS} ( count ( - I have QV 11.20 - this is underlined by QV as wrong, but it's giving results (different than other)

I have also tried with '1' instead of $ in set analysis.

Can anyone help how to keep Year in Set Analysis and AGGR DIM ?

Re: AGGR + Set analysis with the same DIM

tresB — Wed, 13 Dec 2017 10:47:29 GMT

What if you use set in aggr() like:

sum({SET ANALYSIS} aggr ( {<Year>} count (

Edit: Of course, if your field is RoK, then the expression would be like: = sum({SET ANALYSIS} aggr ( {<RoK>} count (

Re: AGGR + Set analysis with the same DIM

karolina_ — Wed, 13 Dec 2017 10:54:41 GMT

Almost
It is not working but this is:

sum({<Year={$(=if(GetSelectedCount(Year)=1,Only(Year),vCurrentYear)-1)}>} aggr(Count( {<Year>} DISTINCT

if([Date2]>=MonthStart and [Date2]<=MonthEnd, ID)

),ID, Year, Month, Date2))

(Can you edit your answer so i will mark it?)

Thanks a million!

Re: AGGR + Set analysis with the same DIM

karolina_ — Wed, 13 Dec 2017 14:28:30 GMT

Forgot to whole expression to english

Point is:
correct form is not sum({SET ANALYSIS} aggr ( {<Year>} count (
but sum({SET ANALYSIS} aggr ( count ( {<Year>}

which I would not try without your help, so I asked for edit your expression to mark it as vcorrect.

Re: AGGR + Set analysis with the same DIM

tresB — Thu, 14 Dec 2017 04:44:10 GMT

In general the set component in aggr(), like

Aggr( {Set Analysis} Count(...

is equivalent to (unless you are using older version of qv than 12)

Aggr( Count ( <Set Analysis> ....

Have you tried the one I suggested? Isn't it working?

Re: AGGR + Set analysis with the same DIM

karolina_ — Thu, 14 Dec 2017 07:24:00 GMT

I am using 11.20. Aggr( {Set Analysis} Count( - is marked as wrong in expression, but it is giving results. However not for previous year when Year is selected.

Only Aggr( Count ( <Set Analysis> .. gives results