Understanding aggregation or set analysis in this example?

[josephinetedesc]

Hi all

A person can have a

dateCT

dateQA

a pathway (The pathway determines the Intent, a Clinic, and a Technique)

dateQA - dateCT = turnaround

Using a pivot table I get a table showing what I want

Pathway     Person     (dateQA - dateCT)

However the pain starts when I then try to get an average of turnaround ie (dateQA - dateCT) per Pathway or per Clinic or per Technique.  As soon as i remove the pat_ID1 as a dimension the whole pivot table disappears

I have tried

=(Aggr(Sum(num((dateQA - dateCT))),Clinic))      I get 0

When I had this problem before - I need to set the turnaround in the loading script - I will try that again.

Thank you any help you can give me towards understanding what is happening.

Jo

[Oleg_Troyansky · Accepted solution]

You need to use a nested aggregation with AGGR. The dimensions of AGGR should define the most detailed level of data - it looks like in your case, Pathway and Person is that level of detail.

Since you are using (dateQA - dateCT) in your Pivot table, I assume that the dates are unique per Pathway and Person. If they are not, you can add a more detailed field, such as Encounter ID or something like that.

so, the formula could look like this:

avg(

    AGGR(

         only(dateQA) - dateCT),

    Pathway, Person, ... add any other dimensions here)

)

If you can make it Milan this April, come and listen to my lecture on the use of AGGR() and Set Analysis at the Masters Summit for Qlik!

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business.

[josephinetedesc]

Hi Oleg

I would love to come to Milan – in fact I will be in Milan on the 20th – so I just miss it ☹ (where is it next year – has this been announced ???)

The info you gave below works - I changed one line to be

only(dateQA – dateCT)

The way you wrote the lines out helped my brain compute …

Will certainly check out your book !

Jo

avg(

AGGR(

only(dateQA) - dateCT),

Pathway, Person, ... add any other dimensions here)

)

[jagan]

Hi,

Try this by adding

=Sum((Aggr(Sum(num((dateQA - dateCT))),Clinic, pat_ID1 )))

OR

=Avg((Aggr(Sum(num((dateQA - dateCT))),Clinic, pat_ID1 )))

Hope this helps you.

Regards,

Jagan.

[josephinetedesc]

Hi Oleg

1. I would love to come to Milan – in fact I will be in Milan on the 20^th – so I just miss it  L  (where is it next year – has this been announced ???)

The info you gave below works  - I changed one line to be

only(dateQA – dateCT)

The way you wrote the lines out helped my brain compute … 

Will certainly check out your book !

Jo

avg(

    AGGR(

only(dateQA) - dateCT),  

    Pathway, Person, ... add any other dimensions here)

)

[josephinetedesc]

Thank you

It is good to see the way aggr works!

Jo

[Oleg_Troyansky]

Hi Jo,

I'm glad that you could solve the problem!

I always recommend to write complex expressions in a structural format - each function begins in a new line, tabulated. This way, it's easier to read and to comprehend.

It's a pity that you should miss the Masters Summit just by a little bit. This year, we will be in South Africa in September and in Austin, Texas in October - maybe you could consider one of these destinations? Next year, we will certainly be somewhere in Europe and somewhere in the US, but the specific destinations weren't set yet. Stay tuned for the future updates!

cheers,

Oleg Troyansky