topic Re: Set Analysis - how to sum dynamically! in QlikView

Set Analysis - how to sum dynamically!

Mon, 21 Jul 2014 17:32:03 GMT

Hi,

I am not getting it somebody help me how to solve below calculation!

I have a table like below

Satff.id Staf.name location qty patient

1 A AA 1 X

2 B BB 2 Y

3 C CC 1 Z

1 A BB 1 M

1 A CC 2 N

2 B CC 2 O

Front end filter staff name.

if I select a staff name I need to show the sum(qty) of all clinics associated to that name.

Ex: if I select A Sum(qty) = 9 because he is associated with all the locations

if I select B sum(qty) = 8 because he is associated to BB and CC locations.

if I select C sum(qty) = 5 because he is associated to CC location only.

Re: Set Analysis - how to sum dynamically!

Anonymous — Mon, 21 Jul 2014 17:37:23 GMT

SUM(AGGR(SUM(qty),location))

Re: Set Analysis - how to sum dynamically!

avinashelite — Mon, 21 Jul 2014 17:39:53 GMT

hi santosh,

sum(qty) will give you the same result rite.

What s the issue your facing

Regards,

@vi

Re: Set Analysis - how to sum dynamically!

joshabbott — Mon, 21 Jul 2014 17:57:59 GMT

How from the above do we know that 'A' is associated with all records, and 'B' is associated with BB and CC?

Re: Set Analysis - how to sum dynamically!

Mon, 21 Jul 2014 18:17:58 GMT

One option to achieve what you are trying to do is to build a separate dimension for location. For example:

data:

LOAD * Inline

[

Staff.id,Staff.name,location,qty,patient

1,A,AA,1,X

2,B,BB,2,Y

3,C,CC,1,Z

1,A,BB,1,M

1,A,CC,2,N

2,B,CC,2,O

];

location:

LOAD location,

qty

Resident data;

Drop Field qty From data;

This will provide the type of calculation you are looking for. Review the attached test file.

Re: Set Analysis - how to sum dynamically!

Mon, 21 Jul 2014 18:35:13 GMT

Hello, i actually do not get this clearly how from the above do we know that 'A' is associated with all records, and 'B' is associated with BB and CC? .Lior, please kindly let me know once you get a solution to this as am new to Qlikview.Thanks

Re: Set Analysis - how to sum dynamically!

Mon, 21 Jul 2014 20:30:44 GMT

Let’s try to explain this with some pictures.

In the single table structure, when staff A is selected, only the location and qty fields that are directly associated with it are returned as illustrated below. So even though location AA, BB and CC have a few different values, they are not directly associated with A.

By separating location and qty in its own table, when staff A is selected, through the location key that associates both tables, all qty records for location AA, BB, and CC are returned. As you can see in the below diagram, staff A is associated with location CC in the staff table. The location key and CC are associated with location key in the location/qty table, and therefore all the qty values in the location/qty table for CC are being returned.

There may be other ways to achieve this by having them in the same table, but this is one option that I can think of.

Hope this helps.

Re: Set Analysis - how to sum dynamically!

Thu, 24 Jul 2014 18:04:39 GMT

Hello Lior,thanks very much for this explanation indeed very helpful .Cheers.

Kind Regards ,

Ola.