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

[Anonymous]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

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?

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[baltazarj]

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.