topic Make dynamic groups and count elements in each group in QlikView

Make dynamic groups and count elements in each group

Tue, 06 Apr 2010 15:23:34 GMT

Hello,

I have a QlikView riddle, that prevent me to sleep.

Lets imagine I have products sorted by a main category ("Eatable", "Transport"), and a sub category ("Fruit", "Vegetable" for the "Eatable" and "Bike", "Car" for the "Transport").

I have a price for each product, and a "sales" table that give me the number of item sold.

With the formula

sum(aggr(Sum(Qtt) * only(Price), Prod))

I have the sum of the sales, and even if I collapse my dimension, I have the right sum.

Now, the riddle... 🙂

I want to make a classification between my products : the products having a sum of sales equal to "0", the products having a sum of sales > 0 and < 50, and the products having a sum of sales >= 50. The classification is labeled "none", "few", and "many".

Now, I would like to make a pie chart displaying for each "MainCateg" or "Categ" the count of products labeled "none", "few", "many".

If I'm on the "MainCateg" "Eatable", I want to see a pie with :
- 1 "none"
- 2 "few"
- 1 "many"

To do that, I have to make a calculated dimension that gives "none" / "few" / "many".

I've tried many things, but I don't have a result that allows me to drill down from "MainCateg" to "Prod".

Do you have any idea how to do that ?

I attached the QVW file I work on.

Make dynamic groups and count elements in each group

Thu, 08 Apr 2010 17:00:30 GMT

I still don't have the drill down feature I want, but I managed to have the dynamic dimension.

Make dynamic groups and count elements in each group

vupen — Mon, 12 Apr 2010 08:52:22 GMT

Here is the soloution to your scenario:

Create an INLINE table as below and another table to join using Interval Match


SalesRange:
LOAD * INLINE
[
RangeText;RangeLow;RangeHigh
None;0,0
Few;1,45
Many;46,10000
] (delimiter is ';')
;

JOIN(SalesRange) IntervalMatch(Sales) Load RangeLow, RangeHigh Resident SalesRange ORDER BY RangeLow ASC;

In the above code, I assume 'Sales' as the column name for Sales in your base table.

Make dynamic groups and count elements in each group

d_pranskus — Mon, 12 Apr 2010 09:57:48 GMT

Just add the calculated dimension to the Dimension Group using Button "Add Expression".

Cheers

Darius