conditional count of aggregated records

[#1]

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.

[jolivares]

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

[jolivares]

Try this one, too:

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

[#4]

Perfect. Thanks for your help.

[#5]

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.

[jolivares]

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

[#7]

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)

[jolivares]

Try this one...

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

[jolivares]

Try this one...

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

[#10]

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