3 Replies Latest reply: Jun 7, 2018 7:33 AM by Louise Rossney RSS

    Position of AGGR function in expression (query)

    Louise Rossney

      Hi all,

       

      I am struggling to see a difference in the outputs of the two expressions below. I would be grateful if anyone could enlighten me if there is one. Basically, I want to take one value of (today-discharged_date) for each unique value of claim_identifier and get the median of those that have a claim_status_description of "Awaiting Consultant Claim" or "Awaiting Consultant Invoice".


      Is there a difference between....

       

      Median ( AGGR (sum ({$ <claim_status_description ={"Awaiting Consultant Claim", "Awaiting Consultant Invoice"}>}today()-discharged_date),Claim_Identifier))

       

      and

       

      Median ({$ <claim_status_description ={"Awaiting Consultant Claim", "Awaiting Consultant Invoice"}>} AGGR (today()-discharged_date),Claim_Identifier))



      kind regards,

      Louise

        • Re: Position of AGGR function in expression (query)
          Olivier GAUTIER

          Hi,

           

          I didn't verify but it should be different :

           

          in first exp :

          aggr(sum   take account of set analysis

           

          in second expression :

          not : sum is calculated for all claim status, and then condition on status

           

          according to me first expression is correct

           

          regards

           

          verify on one claim (with different status)  which is correct

          • Re: Position of AGGR function in expression (query)
            Sunny Talwar

            Do you have a single discharged_date per Claim_Identifier? If you don't then the expression should def. give you different result. But if you don't then... you might just get the same result

            • Re: Position of AGGR function in expression (query)
              Louise Rossney


              Thank you Sunny and Olivier for your help

               

              I think I have figured it out....

               

              There is only one discharged_date for each unique claim_id

               

              In the expression below I have summed all the values of (today()-discharged_date) where the claim_status_description is "Awaiting Consultant Claim", "Awaiting Consultant Invoice", these sums are then grouped by unique Claim_Identifier and then the median of these values calculated.

               

              Grouping the sums is the problem and is giving me a value that is too high.


              Median ( AGGR (sum ({$ <claim_status_description ={"Awaiting Consultant Claim", "Awaiting Consultant Invoice"}>}today()-discharged_date),Claim_Identifier))


              In the expression below I am grouping the values of (today()-discharged_date) by unique Claim_Identifier. Then I am getting the median of those values where the claim_status_description is "Awaiting Consultant Claim", "Awaiting Consultant Invoice". This appears to be giving me more sensible answers for my problem.


              Median ({$ <claim_status_description ={"Awaiting Consultant Claim", "Awaiting Consultant Invoice"}>} AGGR (today()-discharged_date),Claim_Identifier))