Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
betthisisnttake
Contributor III
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
Specialist II
Specialist II

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


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