topic Re: conditional count of aggregated records in QlikView

conditional count of aggregated records

Sun, 28 Sep 2014 23:28:44 GMT

Hi,

In the enclosed app, I would like to modify the "<50 expression" formula so that instead of returning the total count of aggregated records for the current fields, it would show the count of records in the aggregation set for those whose "Quantity" expression is less than 50. So for example, in the table we should get the following result for the expression:

w1, A , laptop = 0

w1, A, tv = 0

w1, C, bed = 2

w1,C, chair =2

w1, C, table = 2

w2, A, laptop = 1

w2, A, tv = 1

thanks.

Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 01:06:31 GMT

Let's see if I understand you require: You need to count how many product is under 50 (in quantity) for each warehouse and category. If it is right, try this expression:

Sum(Aggr(NODISTINCT Count(If(quantity<50,product)),warehouse,category))

Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 01:12:44 GMT

Try this one, too:

Sum(TOTAL <warehouse,category> Aggr(Count(If(quantity<50,product)),warehouse,category))

Re: conditional count of aggregated records

Mon, 29 Sep 2014 01:15:31 GMT

Perfect. Thanks for your help.

Re: Re: conditional count of aggregated records

Mon, 29 Sep 2014 01:56:54 GMT

Actually I need to use this expression in this syntax:

sum(Aggr(NODISTINCT Count(If(sum(quantity)<50,product)),warehouse,category))

Note that the if statement is testing against the sum(quantity). qlickview does not like this. I need to check against the sum of quantities of aggregated records for each warehouse, category, product aggregation. You will see that in the revised app with more data, your expression is returning the wrong results.

Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 02:13:26 GMT

In this case you need to group first the sum too...

Re: conditional count of aggregated records

Mon, 29 Sep 2014 04:12:48 GMT

But the expression "Quantity" already has that value? I tried this syntax with no luck:

sum(Aggr(NODISTINCT Count(If([Quantity]<50,product)),warehouse,category))

Here, [Quantity] is the expression that calculates the sum(quality)

Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 04:27:14 GMT

Try this one...

Sum(TOTAL <warehouse,category> Aggr(Count(If(Aggr(Sum(Quantity),warehouse,category)<50,product)),warehouse,category))

Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 04:32:16 GMT

Try this one...

Sum(TOTAL <warehouse,category> Aggr(Count(If(Aggr(Sum(Quantity),warehouse,category,product)<50,product)),warehouse,category))

Re: Re: conditional count of aggregated records

Mon, 29 Sep 2014 05:02:53 GMT

None of them return the desired output. I have uploaded the app with your expressions in it. The desired output should be this:

w1 A laptop = 0

w1 A tv = 0

since this group two products both have sum(quantity) > 50.

w1 C bed = 2

w1 C chair = 2

w1 C table = 2

since this group has two members (chair and table) that have sum(quantity) < 50

Re: conditional count of aggregated records

Mon, 29 Sep 2014 06:30:28 GMT

Try the below expression in <50

Aggr(Count(If(quantity<50,product)),warehouse,category,product)

Re: Re: conditional count of aggregated records

Mon, 29 Sep 2014 07:17:17 GMT

Hi,

I had already tried that expression. it doesn't give the desired output. I need to check for 50 against the sum(quantity), not quantity. Something like the below, except that qv doesn't like it.

=Aggr(Count(If([Sum Quantity]<50,product)),warehouse,category,product)

where [Sum Quantity] is the expression that evaluates the sum(quality)

The desired output should be this:

w1 A laptop = 0

w1 A tv = 0

since this group two products both have sum(quantity) > 50.

w1 C bed = 2

w1 C chair = 2

w1 C table = 2

since this group has two members (chair and table) that have sum(quantity) < 50

Re: conditional count of aggregated records

Mon, 29 Sep 2014 07:24:49 GMT

Not getting.

if this is the case

The desired output should be this:

w1 A laptop = 0

w1 A tv = 0

since this group two products both have sum(quantity) > 50.

Then how below is possible

w1 C bed = 2

w1 C chair = 2

w1 C table = 2

since this group has two members (chair and table) that have sum(quantity) < 50

beacause there is no quantity less than 50 for chair nad table.

Re: conditional count of aggregated records

sagarkharpude — Mon, 29 Sep 2014 07:27:12 GMT

You can also use rank function.

Re: conditional count of aggregated records

rubenmarin — Mon, 29 Sep 2014 07:38:47 GMT

Hi Dennis,

I used Sum(TOTAL <warehouse, category> Aggr(If(Sum(quantity)<50, 1), warehouse, category, product))

Check in attachment if this is what you want.

Re: Re: conditional count of aggregated records

jolivares — Mon, 29 Sep 2014 12:17:01 GMT

I give you the answer...

Sum(Aggr(Count(If(Aggr(Sum(quantity),warehouse,category,product)<50,product)),warehouse,category,product))

Re: conditional count of aggregated records

Mon, 29 Sep 2014 17:35:53 GMT

Your expression worked perfectly. I see now what I was missing. Thanks a lot for taking the time to help me.