topic Rangesum Issue in QlikView

Rangesum Issue

Wed, 09 Mar 2011 10:28:10 GMT

Hi everyone

Hope you are doing well.

I would be very greatful if someone could help me.

Please see the image.

You will see that there is an [ITEM CODE] dimension and a [TOTAL VOLUME] expression.

The [TOTAL VOLUME %] is just a relative expression of [TOTAL VOLUME] and the [CUM VOLUME %] expression is just the cummulative of [TOTAL VOLUME %].

What I want to do is to place all Item Codes which relate to 70% of Total Volume in category A. All Item Codes which relate to the next 20% in category B and all the rest in category C. In other words, the cumulative % in [CUM VOLUME %] <= 0.7 must be category A, 0.71 - 0.90 must be category B and the rest category C.

Is there a way to do this?

The [TOTAL VOLUME] expression is calculated as follow:

sum

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

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

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

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

Rangesum Issue

Miguel_Angel_Baeyens — Wed, 09 Mar 2011 10:41:09 GMT

Hello Christo,

You can use the expressions you already have to get this done with a new expression:

If([*** VOLUME %] <= 0.7, 'A', If([*** VOLUME %] <= 0.9, 'B', 'C'))

Hope that helps.

Rangesum Issue

Wed, 09 Mar 2011 12:07:01 GMT

Hi Miguel

Thank you so much for the feedback.

But I am struggling to do this.

See attached file. Would you please show me how you would do this?

Thanks

Rangesum Issue

Miguel_Angel_Baeyens — Wed, 09 Mar 2011 12:40:20 GMT

Hi,

First, calculate the *** VOLUME % manually with

(sum({$}(INVQuantity*PackSize)) - sum({$}(CNQuantity*PackSize)) + sum({$}(DRUNINVQuantity*PackSize)) - sum({$}(DRUNCNQuantity*PackSize))) / (sum({$} TOTAL (INVQuantity*PackSize)) - sum({$} TOTAL (CNQuantity*PackSize)) + sum({$} TOTAL (DRUNINVQuantity*PackSize)) - sum({$} TOTAL (DRUNCNQuantity*PackSize)))

Although you are using full accumulation, the expression will return a number, so something like

IF([*** VOLUME %] <= .007, 'C', IF([*** VOLUME %] <= .009, 'B', 'A'))

Should work. That .007 should mean 70%, so you may need to accomodate the value to your needs.