8 Replies Latest reply: Aug 23, 2013 3:58 PM by Lan Thong

# Calculate partial sum??

Hi,

So I have this table that I'm trying to add a column that calculate the %of sale by taking salesperson * partial sum of %of sale.  However,  the result is grabbing the  number %ofsale per person but I want it to grab the number from partcial sum. is it possible?

(%ofsale column is from expression, so it's not a field)

Table1

Salesperson       Account          %of sale

Jack                    4                    15%

John                   10                    20%

Mary                    5                    21%

partial sum -      19                       .63

Should be: 4 * .63

10*.63

5*.63

but qlikview is giving me:

4*15%

10*20%

5*21%

• ###### Re: Calculate partial sum??

sounds like you need to use the keyword TOTAL (if you need the overall total), or TOTAL <field> (if you need a subtotal by Field)

Oleg Troyansky

www.naturalsynergies.com

• ###### Re: Calculate partial sum??

Thanks for your help but I've tried that but it's still not right,  it returned 0 value.

• ###### Re: Calculate partial sum??

If you'd like to post a small example, I could try and show you the correct syntax. It's hard to guess what went wrong...

• ###### Re: Calculate partial sum??
 Team SalePerson Account(calculated field) Stage % (calculated field) (sum(total("Stage %"))) * "Account" Red Team Amanda 13 15.79% 0 Red Team Anthony 4 14.29% 0 Red Team Christie 3 50.00% 0 Red Team Fernando 21 12.00% 0 Red Team Jeff 0 100.00% 0 Red Team Jesse 14 16.67% 0 Red Team John 18 22.22% 0 Red Team Joseph 13 14.29% 0 Red Team Joshua 6 36.36% 0 Red Team Nicole 18 % 0 Red Team Tony 22 16.22% 0 Total 132 18.56% 0
 Expression for Stage% num(100*(count({\$}opportunityid)/count({\$}opportunityid))*(count({\$}opportunityid)/count({\$}opportunityid))*(count({\$}opportunityid)/count({\$}opportunityid))*(count({\$}opportunityid)/count({\$}opportunityid)), '00.00') & '%'
• ###### Re: Calculate partial sum??

I'm a bit confused... When you are talking about Table 1 and Table 2, it looks like you are trying to calculate it in a load script. When you are talking about an Expression, it sounds like you are trying to do it in a Chart like a Straight Table...

Anyway, if this is a chart, it's not enough to just refer to your previously calculated  column and add TOTAL to it, to get what you want... TOTAL doesn't work this way. You can't aggregate a column value again. As painful as it is, you have to replicate your original expression and to add the word TOTAL  in each count(), between your Set Analysis and the field:

 Expression for Stage% num(100*(count({\$}TOTAL opportunityid)/count({\$} TOTAL opportunityid))*(count({\$} TOTAL opportunityid)/count({\$} TOTAL opportunityid))*(count({\$} TOTAL opportunityid)/count({\$} TOTAL opportunityid))*(count({\$} TOTAL opportunityid)/count({\$} TOTAL opportunityid)), '00.00') & '%'

This should work...

Oleg Troyansky

www.naturalsynergies.com

• ###### Re: Calculate partial sum??

Can you clarify  if you're trying to create a calculated field in your data model or are you trying to create an expression in your chart?

You say "%ofsale column is from expression, so it's not a field" in which case that would suggest the result you want will be to create an expression in a chart but you start off by saying you are trying to add a column to your table.

If %ofsale is an expression in a chart then I'd probably stick with that route but if you can answer my first question, I might be able to help some more.

Andy

• ###### Re: Calculate partial sum??

I'm trying to create a calculated field.  Do you think it might be easier to join two tables to create a new calculate field?but I'm not sure how to do that either.

Table 1:

 Team Stage 1(calculated field) Red Team 18% Blue Team 10%

Table 2:

 Team Salesperson Account % of sale in stage 1 Red Team John 5 5 * 18% Red Team Jack 12 12*18% Red Team Mary 21 21*18% Blue Team Linda 30 30*10% Blue Team Brian 41 41*10%

Originally, I added the "stage 1(calculated field)" in Table 2 so I can eliminate Table 1 but it would give me the % per person and not per team.  So I just want to multiply the account by the total % per team.

• ###### Re: Calculate partial sum??

Thank you so much for your help!!! It works now