Dimension filter in Nested Set Analysis

nmsoares — Tue, 20 Jun 2017 18:22:44 GMT

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?

Re: Dimension filter in Nested Set Analysis

rubenmarin — Tue, 20 Jun 2017 20:43:07 GMT

Hi Nuno, Set analysis is calculated before the rows and columns so all the dimensions applies the same set analysis , you can try with:

Sum(Aggr(If(Sum(NewUnitClient), Sum(#Invoiced)), Patient_ID, Unit))

Maybe there is a better solutions for this.

Re: Dimension filter in Nested Set Analysis

nmsoares — Wed, 21 Jun 2017 16:31:11 GMT

Hi Ruben,

Thanks for the tip. It works perfectly!

topic Dimension filter in Nested Set Analysis in QlikView

Dimension filter in Nested Set Analysis

Re: Dimension filter in Nested Set Analysis

Re: Dimension filter in Nested Set Analysis