topic Calculate partial sum?? in QlikView

Calculate partial sum??

Thu, 22 Aug 2013 18:47:03 GMT

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%

Thanks for your help!

Re: Calculate partial sum??

Oleg_Troyansky — Fri, 23 Aug 2013 04:27:37 GMT

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

www.naturalsynergies.com

Re: Calculate partial sum??

Fri, 23 Aug 2013 07:44:15 GMT

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

Fri, 23 Aug 2013 16:47:31 GMT

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

Re: Calculate partial sum??

Oleg_Troyansky — Fri, 23 Aug 2013 16:55:47 GMT

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

Fri, 23 Aug 2013 16:59:37 GMT

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

Fri, 23 Aug 2013 17:07:50 GMT

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({$<Movement={'Stage 5 to 6'}>}opportunityid)/count({$<Movement={'Stage 4 to 5'}>}opportunityid))*(count({$<Movement={'Stage 6 to 7'}>}opportunityid)/count({$<Movement={'Stage 5 to 6'}>}opportunityid))*(count({$<Movement={'Stage 7 to 8'}>}opportunityid)/count({$<Movement={'Stage 6 to 7'}>}opportunityid))*(count({$<Movement={'Stage 8 to 9'}>}opportunityid)/count({$<Movement={'Stage 7 to 8'}>}opportunityid)), '00.00') & '%'

Re: Calculate partial sum??

Oleg_Troyansky — Fri, 23 Aug 2013 19:42:36 GMT

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({$<Movement={'Stage 5 to 6'}>}TOTAL opportunityid)/count({$<Movement={'Stage 4 to 5'}>} TOTAL opportunityid))*(count({$<Movement={'Stage 6 to 7'}>} TOTAL opportunityid)/count({$<Movement={'Stage 5 to 6'}>} TOTAL opportunityid))*(count({$<Movement={'Stage 7 to 8'}>} TOTAL opportunityid)/count({$<Movement={'Stage 6 to 7'}>} TOTAL opportunityid))*(count({$<Movement={'Stage 8 to 9'}>} TOTAL opportunityid)/count({$<Movement={'Stage 7 to 8'}>} TOTAL opportunityid)), '00.00') & '%'

This should work...

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

Re: Calculate partial sum??

Fri, 23 Aug 2013 19:58:04 GMT

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