topic Re: Set analysis AND combination in QlikView

Set analysis AND combination

felix18807 — Thu, 13 Jun 2019 11:04:40 GMT

I want to count unique DIMENSION1 where there is a record that has DIMENSION2 = 'VAL 1' AND DIMENSION3 = 'VAL A' but also has a record DIMENSION2 = 'VAL 2' AND DIMENSION3 = 'VAL B'.

Now normally I would attempt this in the script but in this case, I need to look at the situation dynamically (so the user can pick which value combinations are relevant).

Is this possible in set analysis?

Re: Set analysis AND combination

gf — Thu, 13 Jun 2019 11:18:06 GMT

Hey Felix,
In your demo you want to count the number of YES by the selection of the user?
So if i choose
DIM1 = 1
DIM2 = A
DIM3 = CAT1

The expression should return 1?

Re: Set analysis AND combination

anushree1 — Thu, 13 Jun 2019 11:19:35 GMT

Please try:

Count({$<DIMENSION2 = {'VAL 1'}, DIMENSION3 = {'VAL A'} > + < DIMENSION2 ={'VAL 2'} ,DIMENSION3 ={'VAL B'}>} DIMENSION1)

Re: Set analysis AND combination

gf — Thu, 13 Jun 2019 11:21:11 GMT

if you want to count the YES depending on the user's selection try this one:
=COUNT({<DIM1={'$(=GetFieldSelections(DIM1))'} ,DIM2={'$(=GetFieldSelections(DIM2))'} , DIM3={'$(=GetFieldSelections(DIM3))'} >} IF(COUNTME='YES', COUNTME))

Re: Set analysis AND combination

sunny_talwar — Thu, 13 Jun 2019 11:25:16 GMT

Try this

=Count({$<DIM1 = P({<DIM2 = {'C'}, DIM3 = {'CAT5'}>})*P({<DIM2 ={'A'}, DIM3 = {'CAT1'}>})>} DISTINCT DIM1)

Re: Set analysis AND combination

felix18807 — Thu, 13 Jun 2019 12:01:10 GMT

Hi Gf,
Sorry my ability to articulate the problem isn't great and I believe that what you have set out here would work if I had the ability to do the COUNTME field but I only included that in the demo file to show the relevant lines. I can't do this in the real example.

Some context might help - we're looking at a bunch of students that have achieved grades A-E by months. I'm trying to create a visualisation that shows how many students that were achieving grade Bs in May 2019 and getting As in Jun 2019 based on selection:

Re: Set analysis AND combination

felix18807 — Thu, 13 Jun 2019 12:03:01 GMT

This seems to work.. That is some fantastic set analysis witchcraft. I'll look up the P() and what that is up to.

Thank you sir.