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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Thanks in Advance

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

sunny_talwar — Tue, 15 Aug 2017 15:39:46 GMT

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

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

Anonymous — Wed, 16 Aug 2017 13:19:10 GMT

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?

sunny_talwar — Wed, 16 Aug 2017 13:37:35 GMT

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

What 2 parameters? Not sure what you mean?

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

Anonymous — Thu, 17 Aug 2017 06:53:14 GMT

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?

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

Anonymous — Thu, 17 Aug 2017 11:58:13 GMT

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!

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

sunny_talwar — Thu, 17 Aug 2017 15:11:34 GMT

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?

sunny_talwar — Thu, 17 Aug 2017 15:37:18 GMT

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

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

Anonymous — Fri, 18 Aug 2017 05:41:21 GMT

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

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

sunny_talwar — Fri, 18 Aug 2017 11:28:38 GMT

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

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

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

Anonymous — Fri, 18 Aug 2017 11:54:56 GMT

Yes, Blank or "-" whatever should come but above calculation should be the output

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

sunny_talwar — Fri, 18 Aug 2017 11:59:59 GMT

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?

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

Anonymous — Fri, 18 Aug 2017 12:04:31 GMT

Okay.. but sometimes i don't get one or two out of these.. That's fine.. Thanks for your help!

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

sunny_talwar — Fri, 18 Aug 2017 12:14:54 GMT

May be there is a mismatch in the name either in the Match statement or within the set analysis may be? Difficult for me to say without having a look at this

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

Anonymous — Fri, 18 Aug 2017 12:20:24 GMT

Sure.. You may have a look.. I'm tired of finding bugs..

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

sunny_talwar — Fri, 18 Aug 2017 12:28:26 GMT

Once again, I fail to understand the use of dummy data with original application?

Also, some of the things are not available here

Asbestos Removal and Auction Services are missing from Class or Group....

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

baltazarj — Tue, 15 Oct 2019 08:12:24 GMT

I have experience now the same issue. I got my answer after I select up to the third filter. I have wrong answer when I only select something from first and second filter. Why do I need to select up to the third filter? I also want to get my answer even I only first or up to second filter.

Thank you in advance.