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: 
belromvar
Contributor II
Contributor II

Count if sum(value) ist not 0

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. 

ProjectProductTypeAmount
S3P_ABUD100
S3P_BBUD15
S3P_DBUD50
S3P_DBUD-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?

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Is this what you are looking to get?

image.png

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)

View solution in original post

16 Replies
sunny_talwar

Is this what you are looking to get?

image.png

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
Contributor II
Contributor II
Author

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)

 

dist.PNG

sunny_talwar

You want this?

image.png

belromvar
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

sunny_talwar

Like this?

image.png