Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
cctien11
Partner
Partner

Set Analysis in Data Load Script

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!

1 Solution

Accepted Solutions
cctien11
Partner
Partner
Author

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

View solution in original post

1 Reply
cctien11
Partner
Partner
Author

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

View solution in original post