5 Replies Latest reply: Feb 7, 2012 10:04 AM by Anil Chakelam RSS

    Problem in Set analysis count condition



             For one chart am using set analysis to get count of tickets closed in a month and count of tickets closed as application catageory,, fo rthis am using combo chart. for month wise tickets its giving correct count for this am using the below expression


      =if((C_TCKT_STAT ='C'),(Count({1<CalendarDate={">=$(=addmonths(monthend(max(CalendarDate))+1,-15)) <=$(=Max(CalendarDate))"}>}I_TCKT)))


      For application catageory its not giving correct count


      =if((C_TCKT_STAT ='C'),(Count({1<CalendarDate={">=$(=addmonths(monthend(max(CalendarDate))+1,-15)) <=$(=Max(CalendarDate))"},N_APPL_CATGY1={Applications}>}I_TCKT)))


      both expressions showing same same count.


      here i have attached sample file






        • Problem in Set analysis count condition
          Erich Shiino



          I believe you can move the if condition to your set analysis expression:


          =(Count({1<CalendarDate={">=$(=addmonths(monthend(max(CalendarDate))+1,-15)) <=$(=Max(CalendarDate))"} , C_TCKT_STAT={'C'}>}I_TCKT))


          Also, this part of your set analysis N_APPL_CATGY1={Applications} will have no effect on your results since the table Sheet1 is not connect to the others in your data model.


          You need to find a filed to properly associate the tables via script.

          Hope this helps,



          • Problem in Set analysis count condition
            jagan mohan rao appala

            Hi Anil,


            Check that the field  'N_APPL_CATGY1' is in Island table, ie., it has no mapping with the other tables.  If there is association between these tables then data would change automatically.




            • Problem in Set analysis count condition

              Hi Anil,

              your table "Sheet1" is an data island (no connection to your data model).

              Do you see a way to connect this table to your data model?

              Is there a field you can use in your source data?



              • Problem in Set analysis count condition

                Hi Anil


                besides that the text Applications should be surrounded by single quotes, since it is a literal not a variable I believe your real problem is in the load script: there is no relationship between the Calendar-1 table which you use for the count and the Sheet1 table you use for the set filter. You can actually fill anything between the brackets in stead of "Applications" and get the same count.


                I'm guessing there should be some kind of relationship between tickets and categories. This is done in the load script by making sure the two tables have a column name in common.