Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Leadership Calculation

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

2 Replies
sunny_talwar
Not applicable

Re: Leadership Calculation

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

Re: Leadership Calculation

It works like a charm.

Thanks Sunny!