topic Re: problems with set analysis union in QlikView

problems with set analysis union

Wed, 06 Jun 2012 12:39:20 GMT

Hi,

I'm having the following problem with a union when using a set analysis. I have the following expression:

=count({<InResPlaGrW={">0"}> + <OutResPlaGrW={">0"}> + <InValResPlaGrW={">0"}> + <OutValResPlaGrW={">0"}>} DISTINCT #CalendarDate)

The purpose of the expression to count the number of days where and/or the fields InRes/OutRes/InValRes/OutValRes > 0.

In some weeks this works fine in others it doesn't and I get a count of 0 even though there are days where at least one of the values is greater then 0.

If I look at the results for a single week I get the following results for the union expression and the set analysis expressions individually:

=count({<InResPlaGrW={">0"}> + <OutResPlaGrW={">0"}> + <InValResPlaGrW={">0"}> + <OutValResPlaGrW={">0"}>} DISTINCT #CalendarDate) = 0

=count({<InResPlaGrW={">0"}> } DISTINCT #CalendarDate) = 5

=count(<OutResPlaGrW={">0"}>} DISTINCT #CalendarDate) = 5

=count(<InValResPlaGrW={">0"}> DISTINCT #CalendarDate) = 5

=count(<OutValResPlaGrW={">0"}>} DISTINCT #CalendarDate) = 5

In my understanding this shouldn't be possible since the union of the sets should always contain more data then the individual sets. Hope anyone can clarify this issue. Tnx!

problems with set analysis union

jagan — Wed, 06 Jun 2012 13:01:57 GMT

Hi,

Can you attach the sample file, I think your expression seems to be correct.

I used sample expression like this, It works for me

=sum({<F1={a}> + <F2={200}>} F2)

Hope this helps you.

Regards,

Jagan.

problems with set analysis union

CELAMBARASAN — Wed, 06 Jun 2012 13:21:58 GMT

Hi,

Check whether these fields are in different tables InResPlaGrW, OutResPlaGrW, InValResPlaGrW, OutValResPlaGrW

With #CalendarDate field is in common to join these fields.

If so check whether dates returned by the below expression are same

=Concat({<InResPlaGrW={">0"}> } DISTINCT #CalendarDate,',')

=Concat(<OutResPlaGrW={">0"}>} DISTINCT #CalendarDate,',')

=Concat(<InValResPlaGrW={">0"}> DISTINCT #CalendarDatem,',')

=Concat(<OutValResPlaGrW={">0"}>} DISTINCT #CalendarDate,',')

Celambarasan

problems with set analysis union

Thu, 07 Jun 2012 09:17:44 GMT

The fields are all in the same table which is connected to the mastercalendar which contains the field #CalendarDate. The Concat command in some cases give the same output in some it doesn't but this is not correlating with the function working or not working.

Maybe if I explain the tables it is easier to understand what i want to achieve:

Lines:

#Keyfield InRes OutRes InValRes OutValRes

1 1 0 0 1

2 0 1 0 1

3 1 0 0 0

4 0 1 0 1

5 0 0 1 0

6 0 0 0 0

7 1 0 0 0

MC:

#Keyfield #CalendarDate

1 1

2 1

3 2

4 3

5 4

6 5

7 4

I want to count the distinct number of calendardates where at least 1 of the fields is > 0. So in this case the answer would be 4. One option could be to at a field in the script which is the Sum() of all 4 but is it also possible with set analyis? Hope you can help.

Re: problems with set analysis union

swuehl — Thu, 07 Jun 2012 14:09:27 GMT

dposthuma,

I tried to reproduce your issue with your given data, but failed (tested with QV 10 and QV11, which version are you using?).

Attached my sample of your data and expressions, seems all ok to me.

Regards,

Stefan

Re: problems with set analysis union

Fri, 08 Jun 2012 09:29:46 GMT

The issue seems to be that #CalendarDate is a keyfield. If I use the function with the field CalendarDate (Copy of #CalendarDate within the MC) it works fine. Still I don't understand how this could affect the functionality since the set analysis still selects the set.