topic Cumulative categories in QlikView

Cumulative categories

Fri, 04 Mar 2011 09:24:13 GMT

Hi everyone

You will see attached that I have ItemCodes with a Volume Figure and a Relative %.

I want to place my ItemCodes into 3 categories based on their volumes.

I want the ItemCodes that contribute 70% to TOTAL VOLUME to be category A (0% - 70%).

I want the ItemCodes that contribute 20% to TOTAL VOLUME to be category B (70.1% - 90%).

I want the ItemCodes that contribute 10% to TOTAL VOLUME to be category B (90.1% - 100%).

Do anyone maybe have a way to do this.

Thank you

Cumulative categories

deepakk — Fri, 04 Mar 2011 10:26:33 GMT

hi,

PFA, You data doesnt satify the last two conditions. I hope the attach application is what you are looking for.

Cumulative categories

stephencredmond — Fri, 04 Mar 2011 10:35:10 GMT

Is this something you want in the load script or to be dynamic?

You could try a calculated dimension:

=if(aggr(RangeSum(Above(Sum([TOTAL VOLUME]), 0, RowNo())) / Sum(total [TOTAL VOLUME]),[ITEM CODE]) <= .7, 'Category1', if(aggr(RangeSum(Above(Sum([TOTAL VOLUME]), 0, RowNo())) / Sum(total [TOTAL VOLUME]),[ITEM CODE]) <= .9, 'Category2', 'Category3'))

Regards,

Stephen

Cumulative categories

Fri, 04 Mar 2011 11:21:09 GMT

Hi Stephen

Thanks for the feedback. I would like this to be dynamic as I would like to see which products is category A,B or C depending on the date range I select.

I copied your dimension into my model. The result is that all my categories are category 3.

This is the expression I use to get TOTAL VOLUME:

sum

({$<InvntItem = {$(='Y')}>}(INVQuantity*PackSize))

sum

({$<InvntItem = {$(='Y')}>}(CNQuantity*PackSize))

sum

({$<InvntItem = {$(='Y')}>}(DRUNINVQuantity*PackSize))

sum

({$<InvntItem = {$(='Y')}>}(DRUNCNQuantity*PackSize))

This is the ITEM CODE field name: ItemCode

Would you please help me how to incorporate this information into your solution given before?