Position of AGGR function in expression (query)

[betthisisnttake]

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

[ogautier62]

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

[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

[betthisisnttake]

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))