Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

static_abhi
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

ClassSum of CountCalculationFormula
A171471202.43%A17/(CP1+CT1+B20)
A18971021.61%A17/(CP1+CT1+B20)
B20F95990.16%A17/(CP1+CT1+B20)
A3120180.20%A17/(CP1+CT1+B20)
A27771114.91%A2/B2
A123436144.52%A12/B12
A829614.18%A8/B8
A614863121.85%A6/B6
CP11577703
CT11966106
B202501229
B2521042
B1277174
B82087
B612198

Thanks in Advance

1 Solution

Accepted Solutions

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

Capture.PNG

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

View solution in original post

16 Replies

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]))))))

Capture.PNG

static_abhi
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..

Can you please help providing an alternate solution?

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

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?

static_abhi
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?

static_abhi
New Contributor II

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

Okay this is the original dataset!

  

ClassCount
Land Surveying24396
Appliance Repair - Small155422
Landscaping24036
Landscaping & Lighting29726
Landscaping - Hardscaping & Pavers592
Appliance Sales68722
Landscaping - Lakefront1042084
Lawn & Yard Work19198
Lawn Fertilization & Treatment154348
Artwork/Murals3932212
Architects & Building Design3155406
Appraisals - Real Estate5002458
Oriental Rug Cleaning4174
Appliance Refinishing294240
Appliance Repair - Large194204

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))))))

scrShot.png

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!

Highlighted

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!!!

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

static_abhi
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:

GroupOrders
Appraisals - Real Estate1545
Landscaping & Lighting1642
Appliance Refinishing715
Landscaping & Lighting220
Architects & Building Design1721
Appliance Repair - Small325
Lawn & Yard Work15
Landscaping - Lakefront1557
Architects & Building Design1255
Appliance Refinishing1076
Appliance Repair - Large1228
Landscaping - Lakefront1326
Landscaping & Lighting925
Oriental Rug Cleaning1881
Appliance Refinishing461
Landscaping1714
Landscaping - Hardscaping & Pavers510
Lawn Fertilization & Treatment269
Landscaping - Hardscaping & Pavers1110
Appliance Sales1666
Landscaping - Lakefront635
Lawn Fertilization & Treatment306
Lawn & Yard Work1027
Appraisals - Real Estate324
Appliance Repair - Large164
Landscaping1640
Appliance Sales1648
Artwork/Murals648
Appliance Repair - Small0
Landscaping - Hardscaping & Pavers701
Oriental Rug Cleaning535
Appliance Repair - Small735
Land Surveying322
Architects & Building Design1023
Artwork/Murals1502
Land Surveying1242
Lawn & Yard Work471
Appraisals - Real Estate533
Landscaping585
Appliance Repair - Large658
Land Surveying516
Lawn Fertilization & Treatment1498
Artwork/Murals573
Oriental Rug Cleaning1812
Appliance Sales1321
Asbestos Removal1498
Auction Services9501

What I want:

  

Row Labels (Group)Sum of OrdersCalculationFormula for Calculation
Appliance Refinishing225224.68%Appliance Refinishing/sum(Appraisals - Real Estate, Architects & Building Design, Artwork/Murals)
Appliance Repair - Large205022.47%Appliance Repair - Large/sum(Appraisals - Real Estate, Architects & Building Design, Artwork/Murals)
Appliance Repair - Small106070.76%Appliance Repair - Small/Asbestos Removal
Appliance Sales463548.78%Appliance Sales/Auction Services
Appraisals - Real Estate2402
Architects & Building Design3999
Artwork/Murals2723
Land Surveying2080
Landscaping3939
Landscaping - Hardscaping & Pavers2321
Landscaping - Lakefront3518
Landscaping & Lighting2787
Lawn & Yard Work1513
Lawn Fertilization & Treatment2073
Oriental Rug Cleaning4228
Asbestos Removal1498
Auction Services9501

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

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?

Capture.PNG

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