Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
It works like a charm.
Thanks Sunny!