topic Distinct count aggregation in hierarchies in QlikView

Distinct count aggregation in hierarchies

Mon, 23 Jan 2012 12:39:02 GMT

Maybe that's a newbie question but I'm unable to find an answer to it.

It's very simple : I've got a hierarchies of categories and I show them in a list with an associated distinct count expression.

so far, it's ok.

I would like to add now an expression that calculate the distinct count on the node itself and its children node.

I cannot just sum the distinct count, because an item can be classified in multiple categories.

So for instance if a have a node N1 and 2 subnodes N1.1 and N1.2, and suppose that I have item I1 associated to N1, I2 associated to N1.1, and I2 and I3 asociated to N1.2, I want as a result :

N1 : 1 distinct item, 3 distinct items in total (including the subnodes)

N1.1 : 1 distinct item, 1 in total

N1.2 : 2 distinct items, 2 in total

Thanks for your help

Distinct count aggregation in hierarchies

nstefaniuk — Mon, 23 Jan 2012 12:51:16 GMT

Hi.

How is your model please?

Distinct count aggregation in hierarchies

forte — Mon, 23 Jan 2012 13:00:31 GMT

Hi vincentsc :

I think having your model would be usefull, but i suspect you need aggr function. In example :

sum(count(DISTINCT ITEM), N1, N2))

, I've supposed you have stored nodes on N1, N2 fields and Item level on a field called ITEM. Aggr function will do the count for each pair N1-N2 and then you only need to sum that values.

Hope it helps,

Regards

Distinct count aggregation in hierarchies

Mon, 23 Jan 2012 13:37:59 GMT

Hi,

Thank you very much for your quick replies.

Actually my hierarchies are notregular in the sense that all categories don't have allthe same number of descendants.

The hierarchies are stored in atable where each line correspond to a node and contains the category id, itsname, parent id and depth : ID_CATEGORY NAME_CATEGORY PARENTID_CATEGORYDEPTH_CATEGORY.

I load the hierarchy like this:

Hierarchy(ID_CATEGORY, PARENTID_CATEGORY, NAME_CATEGORY, "RESULT_CATEGORY_PARENT", NAME_CATEGORY, "RESULT_CATEGORY_PATH", ">", "RESULT_NOMEN_DEPTH")

I have then a table of listing"events" caused by a user and related to products: ID_EVENTS, ID_USER,ID_PRODUCT and a join table that associate products to categories.

At the end, for each categoryof my hierarchies, I want to count the distinct number of products and thedistinct numbers of users, for the category and for the category and itschildren.

Thanks again for you help !