7 Replies Latest reply: Jul 27, 2012 7:48 AM by Sokkorn Cheav RSS

    Set analysis : Dates comparaison

      Dear all,

       

      I uploaded data in a QV tables :

      - 'Calls' with the following fields : C_call_id, C_creation_date, C_finish_date

      I created 1 QV object :

      - a simple table (T1) with the following dimensions : C_call_id, C_creation_date, C_finish_date

       

      In the table T1 I added the following set analysis as expression

      SA1 : =Count({<C_creation_date=C_finish_date>}C_call_id)

       

      I want to get the whole callid that have creation date equal to finish date

       

      Unfortunatly, it doesn't work : I get no line at all in my table T1

      Could you please tell me how I can correct my set analysis to get what I want ?

       

      Thank you

       

      Regards

       

      Pascal

        • Re: Set analysis : Dates comparaison
          Rahul Lakhina

          try like:

           

          Count( if( C_creation_date=C_finish_date,C_call_id,0 )

           

          This is asuming that both the date fields are not date and time fileds.

           

          Best of luck!

           

          Regards

           

          R

          • Re: Set analysis : Dates comparaison
            Sokkorn Cheav

            Hi Pascal,

             

            Not sure this one can do your job. But let try

            1. Count({<C_creation_date = {"=$(=C_finish_date)"} C_call_id)

            2. IF(C_creation_date=C_finish_date,COUNT(C_call_id))

             

            Regards,

            Sokkorn

              • Re: Set analysis : Dates comparaison

                Hello,

                 

                1. Sorry but the first solution doesn't work (I get no lines).

                2. The second solution works : it gives 1 for every callid where the creation date is equal to the finish date but... since it not a set analysis the total is not equal to the sum of '1' occurences (I get a '-' instead). It is really important for me to work with a set analysis because I need to get the total number of calls with same dates later in my QV application.

                 

                Thank you

                 

                Regards

                 

                Pascal

                • Re: Set analysis : Dates comparaison
                  Sokkorn Cheav

                  Hi Pascal,

                   

                  I got your point. AGGR() function may do this job. Below is the alternative solution

                  1. SUM(IF(C_creation_date=C_finish_date,COUNT(C_call_id)))

                  2. SUM(AGGR(IF(C_creation_date=C_finish_date,COUNT(C_call_id)),C_creation_date))

                  3. SUM(IF(C_creation_date=C_finish_date,1,0))

                   

                  That will be easy, if you can share your app. We look into it together to find a good solution.

                   

                  Regards,

                  Sokkorn

                • Re: Set analysis : Dates comparaison
                  Celambarasan Adhimulam

                  Hi,

                  Try this

                  =Count({<C_call_id={"=C_creation_date=C_finish_date"}>}C_call_id)

                   

                  Hope it helps

                  • Re: Set analysis : Dates comparaison
                    Azam Mullick

                    Hi,

                     

                    Have a look at the attached solution. It doesn't use set analysis, which I don't think will work in this scenario: a call creation date can match many call finish dates on different rows: the row based solution gives coherent results.

                     

                    Thanks,

                    Azam

                     

                     

                    UPDATE:

                    =======

                    Thinking about it some more, a set based analysis could be achived like this:

                     

                    Create a 4the field in T1 called C_call_duration, which is the difference in days between C_creation_date and C_finish_date, then you can select the set where C_call_Duration is 0:

                     

                    count ( {$ <C_call_duration = {0}> } C_call_id)