Set Analysis in Data Load Script

[cctien11]

Hi Qlik Community,

We have a KPI that shows the Average Length of Stay (ALOS) and it's calculated based on the formula listed below Users want to be able to re-use this metric in other apps without having to copy the formula from one to another. Is there a way we can save this metric so it will be as if it were part of the data model?  (We know we can use a Master Measure to share the expression between sheets, but we are talking about between apps here)

sum( 
aggr(max({$<fact_daily_census.admit_discharge_referral_status={'On-Service','Discharged'}>} 
fact_daily_census.onservice_days), [patient_id]) ) 
) 
/ 
count({$<fact_daily_census.admit_discharge_referral_status={'On-Service', 'Discharged'}>} 
distinct [patient_id]) 


Our fact table has data as shown in the table below, which was populated based on basic patient info as shown in Table 2. 

patient_id date count onservice_days flag status 
300 6/26/18 1 1 Referred/Admitted On-Service 
300 6/27/18 1 2 On-Service 
300 6/28/18 1 3 On-Service 
300 6/29/18 1 4 On-Service 
300 6/30/18 1 5 On-Service 
300 7/1/18 1 6 On-Service 
300 7/2/18 1 7 Discharged Discharged 


patient_id referral_date admit_date discharge_date 
300 6/26/18 6/26/18 7/2/18

Qlik support told us we can create a separate QVD to achieve this. Can someone help with the syntax and process here? Thank you very much!

[cctien11 · Accepted solution]

We found the answer.

1. define a variable in data load script

2. save the variable into a qvd file then this variable can be used across different apps.

The syntax is as follows:

Let vALOS =
'sum(
{$'&'<
fact_daily_census.admit_discharge_referral_status={"On-Service","Discharged"}
>}
aggr(max({$<fact_daily_census.admit_discharge_referral_status={"On-Service","Discharged"} >}fact_daily_census.onservice_days), [patient_id])
)
/
count({$'&'<
fact_daily_census.admit_discharge_referral_status={"On-Service", "Discharged"}
>}
distinct [patient_id])';

ALOS:
Load
'$(vALOS)' as vALOS
AutoGenerate 1;

Store ALOS into [lib://QVD/ALOS.qvd] (qvd);