topic Partial sum with a condition in a straight table in QlikView

Partial sum with a condition in a straight table

Thu, 03 Mar 2011 16:56:48 GMT

Hi All,

Thanks everyone for the reply for my previous posts.

In a straight table i have values like Certified Diverse,Classified Diverse and Non diverse in a single dimension called Group.

Is it possible for me to do a partial spend as shown below ?

Example: Certified Diverse 1,228,081,835.09 0.8%

Classified Diverse 1,646,795,072.01 1.1%

TOTAL DIVERSE 2,874,876,907.10 1.9%

Non Diverse 146,918,563,301.52 98.1%

TOTAL SPEND 149,793,440,208.62 100.0%

Thanks.

Partial sum with a condition in a straight table

matt_crowther — Thu, 03 Mar 2011 17:48:18 GMT

I'd look to do that in Pivot Table.

If that's not an option I have in the past used a combination of the rangesum() and above() functions.

Hope that helps,

Matt - Visual Analytics Ltd

Partial sum with a condition in a straight table

Thu, 03 Mar 2011 18:10:13 GMT

Thanks Matt. I am new to Qlikview. Can you please tell me how to use the rangesum() and above() function.

Partial sum with a condition in a straight table

Thu, 03 Mar 2011 19:04:28 GMT

Hi,

Attached is an example with a solutions for your problem.

Hope this helps

Regards!

Partial sum with a condition in a straight table

Thu, 03 Mar 2011 19:30:52 GMT

Thanks a lot Gabriela. It worked.

Partial sum with a condition in a straight table

johnw — Thu, 03 Mar 2011 23:17:53 GMT

I think you'd be better off putting the complexity in the load script instead of in the chart:

Groups:
LOAD
"Diverse Type Group"
,subfield("Diverse Types",', ') as "Diverse Type"
INLINE [
Diverse Type Group: Diverse Types
Certified Diverse: Certified Diverse
Classified Diverse: Classified Diverse
TOTAL DIVERSE: Certified Diverse, Classified Diverse
Non Diverse: Non Diverse
TOTAL SPEND: Certified Diverse, Classified Diverse, Non Diverse
] (delimiter is ':');

And then just building your chart with "Diverse Type Group" as the dimension, sorted in original load order. Then your expressions are a simple sum(Qty) and sum(Percent), and QlikView's associative logic handles the rest.

See attached expansion of Gabriela's example to include this alternative approach.