topic Set Analysis not working with multiple selection in QlikView

Set Analysis not working with multiple selection

Fri, 25 Jul 2014 15:16:23 GMT

I have a Set Analysis in a Pivot Table (and straight table) that works fine if I have one month selected. When I select 2 or more months it gives me 0.

It is a simple code: =count({$<PayDay={"<=$(=LastPayDay)"}>} distinct MemberId)

I can´t seem to figure out what I have to do to make it work.

The data looks like this:

MemberId Payday LastPayDay YearMonth

1 1.1.2014 15.2.2014 201401

2 17.2.2014 15.2.2014 201401

3 31.1.2014 15.2.2014 201401

4 10.2.2014 15.2.2014 201401

5 18.2.2014 15.2.2014 201401

6 25.2.2014 15.3.2014 201402

7 16.3.2014 15.3.2014 201402

If I select Jan 2014 then I get 3 out of the set analyse and if I select Feb 2014 I get 1 But if I select both Jan and Feb 2014 I get 0 in bot columns. But I would like to get 3 and 1.

What am I doing wrong!?

Re: Set Analysis not working with multiple selection

Fri, 25 Jul 2014 16:03:37 GMT

Could you share a sample qvw to help clarify the situation, please?

Re: Set Analysis not working with multiple selection

Fri, 25 Jul 2014 16:06:11 GMT

Hi,

I can see you miss tip MAX() function to be abble and select multiple values.

try with something like this:

count({$<PayDay={"<=$(=Max(LastPayDay))"}>} distinct MemberId)

if it doesn't work try this:

count({$<PayDay={"<=$(=Date(Max(LastPayDay)))"}>} distinct MemberId)

Max() function allow select max value from a list of multiple selection, for example if you select Jan 2014 and Feb 2014 QV takes Feb 2014 for the expression.

Best regards.

PD. Excuse my english.

Re: Re: Set Analysis not working with multiple selection

Fri, 25 Jul 2014 16:28:04 GMT

Thanks for the reply

See attatched file. Just to explain I counting how many members have payed before the last pay day each month.

Hope you understand what I'm trying to do.

Best regards Viktor

Re: Set Analysis not working with multiple selection

PrashantSangle — Fri, 25 Jul 2014 16:33:02 GMT

Hi,

Set Analysis work on chart level not dimension level.

Therefore if you select 2 or more YearMonth and your YearMonth is dimension then

then in set analysis is not able to capture which is the LastPayDay need to take.

You can achieve this through simple If statement.

Like write

COUNT(IF(NEW_PAYDAY>=NEW_LASTPAYDAY,MemberId))

I am attaching testing file also which show you the difference.

Regards

Re: Set Analysis not working with multiple selection

PrashantSangle — Fri, 25 Jul 2014 16:33:47 GMT

Hi,

Set Analysis work on chart level not dimension level.

Therefore if you select 2 or more YearMonth and your YearMonth is dimension then

then in set analysis is not able to capture which is the LastPayDay need to take.

You can achieve this through simple If statement.

Like write

COUNT(IF(NEW_PAYDAY>=NEW_LASTPAYDAY,MemberId))

I am attaching testing file also which show you the difference.

Regards

Re: Re: Re: Set Analysis not working with multiple selection

Fri, 25 Jul 2014 16:36:27 GMT

Hi,

try this:

count({$<PAYDAY={"<=$(=Max(LASTPAYDAY))"}>} distinct MEMBERID)

See attached file.

Best Regards

Re: Re: Set Analysis not working with multiple selection

Mon, 28 Jul 2014 09:19:33 GMT

Thanks to all for your replys. I'm sorry to say that I've not got an useful answer, but probably max dreamer is right, it is not possible to do this on a dimension level. But I find it hard to believe that an simple SQL query like this can not be simulated in Qlikview:

Select YearMonth, count(distinct MemberId), PayDay, last_day(to_date(YearMonth||'01', 'yyyymmdd'))+45 AS LastPayDay FROM MemberFee

where PayDay <= last_day(to_date(YearMonth||'01', 'yyyymmdd'))+45

And PayDay >= to_date('01012014', 'ddmmyyyy')

group by YearMonth, to_date(YearMonth||'01', 'yyyymmdd')+45

This Query gives the number of Members that have payed before the last payday for the year 2014....