Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.
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
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.