QlikView App Dev

[belromvar]

Hi Community,

I have the following scenario.  I want to create a chart that shows the distribution of products within a project.

The condition is that the sum of the amount for a specific type should not be 0. 

Project	Product	Type	Amount
S3	P_A	BUD	100
S3	P_B	BUD	15
S3	P_D	BUD	50
S3	P_D	BUD	-50

I expect a chart with dimension = "2" (count of  different products <> 0) and the expression = 1 (count of projects). 

The dimension looks like this: 

aggr( Count(DISTINCT {<Product = {"=aggr(Sum({<Type = {'BUD'}>}Amount),Project, Product) > 0"}>} Product), Project) 

but it only works when one project is selected. For multiple projects, it shows an empty value. I am attaching a working example of what I have achieved so far. I think I am quite near, any ideas?

[sunny_talwar · Accepted solution]

Is this what you are looking to get?

To do this, add a new field in the script which is a combination of Project and Product

LOAD *,
	 Project&Product as Project_Product_Key;
LOAD * INLINE [
    Project, Product, Type, Amount
    S1, P_A, BUD, 10
    S1, P_B, BUD, 20
    S1, P_C, BUD, 30
    S2, P_A, BUD, 40
    S3, P_D, BUD, -50
    S3, P_D, BUD, 50
    S3, P_A, BUD, 100
    S3, P_B, BUD, 15
    S4, P_A, BUD, 5
    S4, P_A, BUD, -5
    S4, P_A, ACT, 10
    S4, P_A, ACT, 10
    S4, P_B, BUD, 11
    S4, P_C, BUD, 12
    S4, P_D, BUD, 13
    S4, P_E, BUD, 14
    S4, P_A, BUD, 15
];

and then change your dimension to this

=Aggr(Count(DISTINCT {<Project_Product_Key = {"=Sum({<Type = {'BUD'}>}Amount) > 0"}>} Product), Project)

[belromvar]

Hi Sunny,

thanks for your help. How do I need to change my Expression if I add 'Product' as second Dimension? I am not getting the right sum, I tried with 

=Count(DISTINCT {<Product= >}  Project)  

=Count(DISTINCT {<Project_Product_Key = {"=Sum({<Type = {'ACT'}>}Amount) <> 0"}>} Project)

[sunny_talwar]

You want this?

[belromvar]

The expression is wrong. For example,  by 5 products the count of projects should be 1 (S4).

[sunny_talwar]

You want Product as second dimension or Project?

[belromvar]

Product. But I also need the total count of projects for each bar.

[sunny_talwar]

Are you saying that for 5, you want to see 5 stacks but see 1 at the top?

[belromvar]

Exactly. The five stacked products, but 1 representing the project count.

[sunny_talwar]

Like this?