topic Counting Pivot table groupings in QlikView

Counting Pivot table groupings

Mon, 29 Sep 2014 03:00:10 GMT

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)

Re: Counting Pivot table groupings

Mon, 29 Sep 2014 03:30:11 GMT

Hi ,

PFA Qvw . Hope that may help u .

Thanks ,

Saurabh

Re: Counting Pivot table groupings

ecolomer — Mon, 29 Sep 2014 03:39:04 GMT

Here you have my proposal

Re: Counting Pivot table groupings

Mon, 29 Sep 2014 04:01:35 GMT

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?