Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

betthisisnttake
New Contributor III

Position of AGGR function in expression (query)

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

3 Replies
ogautier62
Valued Contributor II

Re: Position of AGGR function in expression (query)

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

MVP
MVP

Re: Position of AGGR function in expression (query)

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
New Contributor III

Re: Position of AGGR function in expression (query)


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