Discussion board where members can get started with Qlik Sense.
I am trying to create a new table that contains the average length of stay by state based on joining the data from the 2 qvd files.
I am doing something wrong and can't figure it out.
FROM [lib://QVD Files/MedPar\Medpar_Nursing_Homes.QVD](qvd);
FROM [lib://QVD Files/MedPar\Medpar_SNF_Data_2014.QVD](qvd);
AVG([LENGTH_OF_STAY]) as ALOS
Resident SNF_Info Group By "Provider State";
Drop Table SNF_Info;
Go to Solution.
Figured it out. You can only have the fields loaded that are either in the Group By or the Aggr,
I had State in the Load but not anywhere else.
You don't calculate average in script,
It should be done in chart as sum (lenghtofstay) / count (servicelinenumber)
Still getting an invalid expression error in the BaseLine section
Didn't notice you were using AVG in script it's a chart function
But your expression should actually be as below
sum (lenghtofstay) / count (servicelinenumber)
That still is not working. Expression error.
drop table SNF_Info;
... may be ?
Have you read this post?
Average – Which average?
Avg() finds the average value of the aggregated data in the expression over a number of records as defined by a group byclause.
The reason I mentioned not to do this in script is because your average would then be STATIC.
In your script your average will be based on ProvideState,ServiceLine ,
To See how they remain static do the below
Create 2 Straight Tables,
Dimension: Provider State , ServiceLine
Expression: sum ([Length of Stay]) / count (ServiceLine)
Now remove dimension ServiceLine from both the tables and see what happens.
Table 2 will dynamically evaluate Average by ProviderState, but Table 1 will continue to show you static values.
IT IS COMPLETELY UP TO YOU TO SHOW STATIC VALUES!