topic Re: AND operation in set analysis in QlikView

AND operation in set analysis

Anonymous — Wed, 12 Oct 2016 20:50:36 GMT

Hello

In the example as attached, I would like to count how many IDs with '*-RAM' and their [Ship Year] is '2009'.

The express 'Dispatch' is given as

Sum(Aggr(Count (DISTINCT {<ID = p({<[Ship year]={'2009'}>}) * p({<ID={'*-RMA'}>})>}ID),CalendarYY,CalendarWW))

The Dispatch count should be zero for CalendarYY = 2009 and CalendarWW = 01.

Somehow, Dispatch shows 8 and it seems the expression only considers the term ID='*-RMA' only.

Where did I make a mistake?

Thanks,

Josh

Re: AND operation in set analysis

sunny_talwar — Wed, 12 Oct 2016 21:07:50 GMT

I think you used Ship year instead of Ship Year. But this also seems to work for the selection. QlikView is case sensitive and they two things are different for it.

Sum(Aggr(Count (DISTINCT {<[Ship Year]={2009}, ID = {'*-RMA'}>}ID),CalendarYY,CalendarWW))

Re: AND operation in set analysis

Anonymous — Wed, 12 Oct 2016 21:11:11 GMT

LOL...that obvious.

Thank you again.

Josh

Re: AND operation in set analysis

Oleg_Troyansky — Wed, 12 Oct 2016 21:20:15 GMT

Hi Josh,

the issue is rather silly - your [Ship Year] was misspelled as [Ship year] and the filter was ignored. Unfortunately, the spell checker doesn't pick it up as an error and doesn't give you a warning. That's a bug.

Besides this typo, watch the level of granularity in your AGGR, compared to your Chart dimensions. Your AGGR appears to be less granular than your chart, which may give you incorrect results.

Learn more advanced techniques about Set Analysis, AGGR, and more, in my book QlikView Your Business.

cheers,

Oleg Troyansky