topic Nested aggr of distinct fields in QlikView

Nested aggr of distinct fields

robertsn — Thu, 25 Apr 2019 14:06:03 GMT

I have data of the following structure;

Date	User ID	Product Group	Product	Value
1/4/19	A	Ducks	#1	1
1/4/19	B	Ducks	#1	1
2/4/19	A	Ducks	#1	1
2/4/19	A	Ducks	#2	2
2/4/19	A	Swans	#3	2
2/4/19	B	Swans	#4	1

Aim is to produce the sum value for each specific date however the sum must include the highest value for each product group that each User ID has used on that date

I.e

01/04/19 = 2 (1+1)

02/04/19 =5 (2+2+1)

Real data is patient sensitive hence the ducks & swans!

I've been try without much success sum, aggr & distinct various combinations of the fields without success.

Any help appreciated as I'm a department of 1 inexperienced operator.

Re: Nested aggr of distinct fields

sunny_talwar — Thu, 25 Apr 2019 14:09:39 GMT

May be this

Sum(Aggr(Max(Aggr(Sum(Value), Date, [User ID], [Product Group])), Date, [User ID]))

Re: Nested aggr of distinct fields

sunny_talwar — Thu, 25 Apr 2019 14:12:33 GMT

Actually, how are you getting 2+2+1 for 2/4? Can you explain which rows are you picking and why?

Re: Nested aggr of distinct fields

robertsn — Fri, 26 Apr 2019 06:57:43 GMT

From 2/4/19,

A Ducks #2 = 2 (largest value)

A Swans #3 = 2

B Swans #4 = 1

Total 5

In the real data;

on a given date....

there will be many patients
- each patient might have multiple different drugs prepared for them
  - each drug can have multiple presentations
    - some of the presentations might take more work to prepare

We grabbing a workload snap shot from a different data set which is primarily financial rather that capacity/workload planning. We have a capacity model that assigns a complexity rating (crudely a time measure) to the preparation of differing drugs and their pharmaceutical presentation.

Using the financial data gives us faster access to workload data, all be it only an approximation, and allows better matching resource to patient load than we can achieve waiting for manufacturing and clinic data feedback.

Ideally I need to....

Total daily workload ('value')

Total by active drug ('Ducks & Swans') - however I need to grab only the hardest preparation (highest 'value') as my capacity (complexity) measure otherwise I will end up with grossly inflation estimated workload which will be valueless in maintaining maximised throughput

Total by patient ('A & B')

Sorry for the long winded explanation!

Re: Nested aggr of distinct fields

sunny_talwar — Fri, 26 Apr 2019 11:19:37 GMT

Try this

Sum(Aggr(Max(Value), Date, [User ID], [Product Group]))

Re: Nested aggr of distinct fields

robertsn — Fri, 26 Apr 2019 11:40:05 GMT

Thanks, that's much closer to where I think the value should be (from looking at the data manually).

Appreciate you taking the time

Re: Nested aggr of distinct fields

sunny_talwar — Fri, 26 Apr 2019 11:41:35 GMT

That's great. I am glad I was able to help