Dear fellow Qlikview users,
I'm working with a database with data from several hospitals (units) and i'm trying to build a pivot table to show the invoiced amount that belongs to the new patients.
In my data model, I have a flag (NewUnitClient) that holds 1 in the first time the client has an activity on that unit and 0 in all other times.
For that reason, i had to put the <NewUnitClient={1}> in a nested set analysis to return the total invoiced amount made by all the patients that were marked as new, otherwise i would only return the sum of invoiced amount of the first appointments.
sum(
{$<
Patient_ID={"=count({$<NewUnitClient={1}>} DISTINCT Patient_ID)>0"}
>}
#Invoiced)
This expression works fine when i'm trying to return the total amount considering all the units or if i only select one of them each time.
However, when i build a pivot table with this expression and the units as dimensions, i'm getting wrong values.
I've noticed that the dimension (units) doesn't filter my nested set analysis. So, the values i obtain in the pivot table are the sum of invoiced amount (for each unit) of all my new clients. For example:
sum(
{$<
Unit={'CentralHospital'},
Patient_ID={"=count({$<NewUnitClient={1}>} DISTINCT Patient_ID)>0"}
>}
#Invoiced)
But what i really want is the sum of invoiced amount (for each unit) of the new clients of that unit. For example:
sum(
{$<
Unit={'CentralHospital'},
Patient_ID={"=count({$<NewUnitClient={1},Unit={'CentralHospital'}>} DISTINCT Patient_ID)>0"}
>}
#Invoiced)
Anyone has an idea of how I could make this work?
