5 Replies Latest reply: Jun 8, 2012 5:29 AM by Donald Posthuma RSS

    problems with set analysis union

    Donald Posthuma

      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 mohan rao appala

          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 Adhimulam

            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
                Donald Posthuma

                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.