Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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]))))))
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?
it's somehow calculating only 2 parameters and not the rest..
What 2 parameters? Not sure what you mean?
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?
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!
I don't know what you mean... may be someone else can offer an alternative solution.... all the best!!!
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
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
This is the exact output you are looking to get here?
The red area is all empty... no percentage numbers required here?