Counting Pivot table groupings

[#1]

Hi,

Can someone help me figure out the two expressions I need to implement for my pivot table. in the attached app I would like the two expressions "# products in category" and "sum Quantity < 50 count" to produce the following results:

warehouse       category       product      sum Quantity        # products in category       sum Quantity < 50 count

   w1                     A                laptop             155                           2                                            0

   w1                     A                tv                      80                           2                                            0

   w1                     C                bed                   134                         3                                             1

   w1                     C                chair                  89                          3                                             1

   w1                     C               table                   28                          3                                             1

So here, "# products in category" is 2 for the first two rows (category A)  since there are two products (laptop and tv) in the A category. The "sum Quantity <50 count" expression is 0 since none of those products have a "sum Quantity" less than 50.

For category C we have three products (bed, chair, and table), so "# products in category" should be 3 here. The "sum quantity <50 count" is 1  since there is 1 product in the group (table)  that has a "sum Quantity" of 28 (less than 50)

[#2]

Hi ,

PFA Qvw . Hope that may help u .

Thanks ,

Saurabh

[ecolomer]

Here you have my proposal

[#4]

Two issues with your solution:

First I can't create the aggregation at script time and drop the original table.

Second I need all rows for w1, C to indicate 1 for the "sum Quantity < 50 count" - Essentially, each member of the group should display the total count of  members that are showing a quantity count of less than 50. I need this information at each row to decide if I should display the quantity count for that row- please look at my original table above.

Can these expressions be accomplished with aggr function?