Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Leadership Calculation

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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

Capture.PNG

Not applicable
Author

It works like a charm.

Thanks Sunny!