Set Analysis - how to sum dynamically!

[#1]

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.

[Anonymous · Accepted solution]

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

[avinashelite]

hi santosh,

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

What s the issue your facing

Regards,

@vi

[joshabbott]

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

[#5]

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. 

[#6]

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

[#7]

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.

[#8]

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

Kind Regards ,

Ola.