# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor II

## How to divide a row with sum of specific rows of same column?

Below is the data, i want to create the calculation column in a straight table/barchart who's formula is given in the Fornlula tab, in my actual data it's a Bar chart and it's in an alternate state and expression for that is below:

sum({[State Bar]<Store=\$::Store>}Count)

My problem is how do i calculate table like this in QlikView:

sum({[State Bar]<Store=\$::Store>}Count)/sum(??????)

So that when i update the alternate state's filter these calculations update accordingly

 Class Sum of Count Calculation Formula A17 147120 2.43% A17/(CP1+CT1+B20) A18 97102 1.61% A17/(CP1+CT1+B20) B20F 9599 0.16% A17/(CP1+CT1+B20) A3 12018 0.20% A17/(CP1+CT1+B20) A2 77711 14.91% A2/B2 A12 34361 44.52% A12/B12 A8 296 14.18% A8/B8 A6 14863 121.85% A6/B6 CP1 1577703 CT1 1966106 B20 2501229 B2 521042 B12 77174 B8 2087 B6 12198

1 Solution

Accepted Solutions
MVP

## Re: How to divide a row with sum of specific rows of same column?

I used the exact same expression (except removing unwanted things)

=Sum(Orders)/

If(Match(Group, 'Appliance Refinishing', 'Appliance Repair - Large'), Sum(TOTAL {<Group = {'Appraisals - Real Estate','Architects & Building Design','Artwork/Murals'}>}Orders),

If(Group = 'Appliance Repair - Small', Sum(TOTAL {<Group = {'Asbestos Removal'}>}Orders),

If(Group = 'Appliance Sales', Sum(TOTAL {<Group = {'Auction Services'}>}Orders))))

to get this

This matches exactly with your Excel... is that not what you want?

16 Replies
MVP

## Re: How to divide a row with sum of specific rows of same column?

May be this

=Sum([Sum of Count])/

If(Match(Class, 'A17', 'A18', 'B20F', 'A3'), Sum(TOTAL {<Class = {'CP1', 'CT1', 'B20'}>}[Sum of Count]),

If(Class = 'A2', Sum(TOTAL {<Class = {'B2'}>}[Sum of Count]),

If(Class = 'A12', Sum(TOTAL {<Class = {'B12'}>}[Sum of Count]),

If(Class = 'A8', Sum(TOTAL {<Class = {'B8'}>}[Sum of Count]),

If(Class = 'A6', Sum(TOTAL {<Class = {'B6'}>}[Sum of Count]))))))

New Contributor II

## Re: How to divide a row with sum of specific rows of same column?

Thanks a lot Sunny, but the problem with this is it's somehow calculating only 2 parameters and not the rest..

May be this is an aggregated data that's why it is not working? Should I provide the aggregated data?

MVP

## Re: How to divide a row with sum of specific rows of same column?

 it's somehow calculating only 2 parameters and not the rest..

What 2 parameters? Not sure what you mean?

Highlighted
New Contributor II

## Re: How to divide a row with sum of specific rows of same column?

okay, somehow only the first condition i.e. "Match(Class, 'A17', 'A18', 'B20F', 'A3'), Sum(TOTAL {<Class = {'CP1', 'CT1', 'B20'}>}[Sum of Count])" is getting executed, so calculation is coming only for those fields, rest of the calculations are not happening.. Is there any alternate solution?

New Contributor II

## Re: How to divide a row with sum of specific rows of same column?

Okay this is the original dataset!

 Class Count Land Surveying 24396 Appliance Repair - Small 155422 Landscaping 24036 Landscaping & Lighting 29726 Landscaping - Hardscaping & Pavers 592 Appliance Sales 68722 Landscaping - Lakefront 1042084 Lawn & Yard Work 19198 Lawn Fertilization & Treatment 154348 Artwork/Murals 3932212 Architects & Building Design 3155406 Appraisals - Real Estate 5002458 Oriental Rug Cleaning 4174 Appliance Refinishing 294240 Appliance Repair - Large 194204

When i perform this calculation:

=100*Sum(Count)/

If(Match(Class, 'Appliance Refinishing', 'Appliance Repair - Large', 'B20F', 'A3'), Sum(TOTAL {<Class = {'Appraisals - Real Estate','Architects & Building Design','Artwork/Murals'}>} Count),

If(Class = 'Appliance Repair - Small', Sum(TOTAL {<Class = {'Asbestos Removal'}>}Count),

If(Class = 'Appliance Sales', Sum(TOTAL {<Class = {'Auction Services'}>}Count),

If(Class = 'A8', Sum(TOTAL {<Class = {'B8'}>}Count),

If(Class = 'A6', Sum(TOTAL {<Class = {'B6'}>}Count))))))

I get the above output:

i.e. Only the first if condition is getting run

Hope it is clear now?

I seriously need to fix this issue, Thanks!

MVP

## Re: How to divide a row with sum of specific rows of same column?

I don't know what you mean... may be someone else can offer an alternative solution.... all the best!!!

MVP

## Re: How to divide a row with sum of specific rows of same column?

You are killing me my friend... you keep on showing me what you are getting, but you don't want to tell me what you want to get. Do you really know what you want?

Also, what is this expression that you are using?

=100*Sum(Count)/

If(Match(Class, 'Appliance Refinishing', 'Appliance Repair - Large', 'B20F', 'A3'), Sum(TOTAL {<Class = {'Appraisals - Real Estate','Architects & Building Design','Artwork/Murals'}>} Count),

If(Class = 'Appliance Repair - Small', Sum(TOTAL {<Class = {'Asbestos Removal'}>}Count),

If(Class = 'Appliance Sales', Sum(TOTAL {<Class = {'Auction Services'}>}Count),

If(Class = 'A8', Sum(TOTAL {<Class = {'B8'}>}Count),

If(Class = 'A6', Sum(TOTAL {<Class = {'B6'}>}Count))))))

Why are you using dummy data with original app? If you seriously need help, you need to seriously start giving me better information

New Contributor II

## Re: How to divide a row with sum of specific rows of same column?

I'm sorry for your trouble, I have attached the Excel file for your ease:

the "data" sheet contains the Raw Data and "happiness score" sheet contains my Calculations(which I want to do in QlikView)

I want to create a summary in Qlik as one I have done in excel, in "happiness score" sheet:

To re-iterate:

What I Have:

 Group Orders Appraisals - Real Estate 1545 Landscaping & Lighting 1642 Appliance Refinishing 715 Landscaping & Lighting 220 Architects & Building Design 1721 Appliance Repair - Small 325 Lawn & Yard Work 15 Landscaping - Lakefront 1557 Architects & Building Design 1255 Appliance Refinishing 1076 Appliance Repair - Large 1228 Landscaping - Lakefront 1326 Landscaping & Lighting 925 Oriental Rug Cleaning 1881 Appliance Refinishing 461 Landscaping 1714 Landscaping - Hardscaping & Pavers 510 Lawn Fertilization & Treatment 269 Landscaping - Hardscaping & Pavers 1110 Appliance Sales 1666 Landscaping - Lakefront 635 Lawn Fertilization & Treatment 306 Lawn & Yard Work 1027 Appraisals - Real Estate 324 Appliance Repair - Large 164 Landscaping 1640 Appliance Sales 1648 Artwork/Murals 648 Appliance Repair - Small 0 Landscaping - Hardscaping & Pavers 701 Oriental Rug Cleaning 535 Appliance Repair - Small 735 Land Surveying 322 Architects & Building Design 1023 Artwork/Murals 1502 Land Surveying 1242 Lawn & Yard Work 471 Appraisals - Real Estate 533 Landscaping 585 Appliance Repair - Large 658 Land Surveying 516 Lawn Fertilization & Treatment 1498 Artwork/Murals 573 Oriental Rug Cleaning 1812 Appliance Sales 1321 Asbestos Removal 1498 Auction Services 9501

What I want:

 Row Labels (Group) Sum of Orders Calculation Formula for Calculation Appliance Refinishing 2252 24.68% Appliance Refinishing/sum(Appraisals - Real Estate, Architects & Building Design, Artwork/Murals) Appliance Repair - Large 2050 22.47% Appliance Repair - Large/sum(Appraisals - Real Estate, Architects & Building Design, Artwork/Murals) Appliance Repair - Small 1060 70.76% Appliance Repair - Small/Asbestos Removal Appliance Sales 4635 48.78% Appliance Sales/Auction Services Appraisals - Real Estate 2402 Architects & Building Design 3999 Artwork/Murals 2723 Land Surveying 2080 Landscaping 3939 Landscaping - Hardscaping & Pavers 2321 Landscaping - Lakefront 3518 Landscaping & Lighting 2787 Lawn & Yard Work 1513 Lawn Fertilization & Treatment 2073 Oriental Rug Cleaning 4228 Asbestos Removal 1498 Auction Services 9501

I hope this will help, the expression that you gave earlier worked for Class(Coded value), but it is not working for the full text(Group).

I really want to solve this problem, you are being so helpful, Thank you is not enough, let me know if I can help you in any way?

Once again Thanks! @stalwar1

MVP

## Re: How to divide a row with sum of specific rows of same column?

This is the exact output you are looking to get here?

The red area is all empty... no percentage numbers required here?