topic Re: Count if sum(value) ist not 0 in QlikView

Count if sum(value) ist not 0

belromvar — Sat, 16 Nov 2024 04:32:55 GMT

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?

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 12:35:25 GMT

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)

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 13:23:43 GMT

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)

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 13:29:00 GMT

You want this?

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 13:37:01 GMT

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

sunny_talwar — Wed, 06 Feb 2019 13:38:26 GMT

You want Product as second dimension or Project?

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 13:42:00 GMT

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

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 13:43:11 GMT

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

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 13:45:04 GMT

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

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 13:45:04 GMT

Like this?

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 14:00:12 GMT

Sorry, not yet, the expression returns always 1.

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 14:00:01 GMT

Would you be able to share a sample where we need to have more than one Project? I think the sample always have 1 project for each count(Product).

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 14:02:40 GMT

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 14:13:16 GMT

Check attached

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 15:06:04 GMT

Hi Sunny,

see attached QVW and image, the count is still not returning the correct value.

Re: Count if sum(value) ist not 0

sunny_talwar — Wed, 06 Feb 2019 15:22:50 GMT

Don't think this is possible. but may be someone might have another trick to do this

Re: Count if sum(value) ist not 0

belromvar — Wed, 06 Feb 2019 16:16:21 GMT

Thanks for the help. I decided to use 2 overlapped charts to achieve the goal. 😃