Leadership Calculation

Thu, 05 May 2016 08:37:47 GMT

Hi,

I can't get my head around this problem and would gladly get some fresh idea to try to help me.

Here is the description of the problem:

I have company based on a certain zipcode that sells in their zipcode, but other zipcode as well.

I want to calculate the number of objets sold in the flower (their zipcode + zipcode adj), but i can not manage to do it.

Here is my test load script:

Company:

LOAD * Inline [

Company,Zipcode

A,1000

B,2000

];

ZipcodeAdj:

LOAD * Inline [

Zipcode,ZipcodeAdj

1000,1001

2000,2001

];

Flower:

LOAD * Inline [

Zipcode,Flower

1000,1000

1000,1001

2000,2000

2000,2001

];

Sales:

LOAD * Inline [

Company,SaleZipcode,Sales

A,1000,10

A,1001,5

A,2000,1

A,2001,2

B,2000,20

B,2001,10

B,1000,2

B,1001,1

];

Then i create a chart (straight table).

What i want to see in this table is :

Company,Flower,[Number of Sales In Flower]

A,1000/1001,15

B,2000/2001,30

But what i get when i create the table is:

A,1000/1001,18

B,2000/2001,33

Here is my expression :

=sum( {<SaleZipcode={$(=concat(DISTINCT Flower,','))}>} Sales)

I can not manage to filter this result by company in the straight table.

It is like the dimension company is ignored when qlikview does his calculation.

Kind Regards,

Alexis

Re: Leadership Calculation

sunny_talwar — Thu, 05 May 2016 15:35:14 GMT

Try with this expression:

=Sum(Aggr(If(Match(SaleZipcode, Flower), Sales), Company, Flower, SaleZipcode))

The issue is that the set analysis is evaluated once per chart. The statement you had within your set analysis included all the Flower and hence it was summing all the sales for the company. With the Aggr(If()) you can do this more dynamically

Re: Leadership Calculation

Thu, 05 May 2016 16:21:55 GMT

It works like a charm.

Thanks Sunny!

topic Leadership Calculation in QlikView

Leadership Calculation

Re: Leadership Calculation

Re: Leadership Calculation