Set Analysis in Data Load Script

cctien11 — Thu, 14 Mar 2019 15:55:05 GMT

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!

Re: Set Analysis in Data Load Script

cctien11 — Wed, 20 Mar 2019 20:38:12 GMT

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

topic Set Analysis in Data Load Script in App Development

Set Analysis in Data Load Script

Re: Set Analysis in Data Load Script