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.

[#2]

Try the below expression in <50

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

[#3]

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

[#4]

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.

[sagarkharpude]

You can also use rank function.

[rubenmarin · Accepted solution]

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.

[jolivares]

I give you the answer...

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

[#8]

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