Set Analysis not working with multiple selection

[#1]

Hi

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!?

[#2]

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

[#3]

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.

[#4]

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

[PrashantSangle · Accepted solution]

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

[PrashantSangle]

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

[#7]

Hi,

try this:

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

See attached file.

Best Regards

[#8]

Hi

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....