
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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%
Thanks for your help!
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your help but I've tried that but it's still not right, it returned 0 value.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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') & '%' |


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much for your help!!! It works now
