Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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.

Highlighted
belromvar
New 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

Re: Count if sum(value) ist not 0

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)
16 Replies

Re: Count if sum(value) ist not 0

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

Re: Count if sum(value) ist not 0

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

Re: Count if sum(value) ist not 0

You want this?

image.png

belromvar
New Contributor II

Re: Count if sum(value) ist not 0

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

Re: Count if sum(value) ist not 0

You want Product as second dimension or Project?

belromvar
New Contributor II

Re: Count if sum(value) ist not 0

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

Re: Count if sum(value) ist not 0

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

belromvar
New Contributor II

Re: Count if sum(value) ist not 0

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

Re: Count if sum(value) ist not 0

Like this?

image.png