Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

problems with set analysis union

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!

Tags (2)
5 Replies
MVP & Luminary
MVP & Luminary

problems with set analysis union

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

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

Not applicable

problems with set analysis union

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.

MVP
MVP

Re: problems with set analysis union

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

Not applicable

Re: problems with set analysis union

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.