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