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

    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!

        • Re: Calculate partial sum??
          Oleg Troyansky

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

              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

                  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??
                      TeamSalePersonAccount(calculated field)Stage % (calculated field)(sum(total("Stage %"))) * "Account"
                      Red TeamAmanda1315.79%0
                      Red TeamAnthony414.29%0
                      Red TeamChristie350.00%0
                      Red TeamFernando2112.00%0
                      Red TeamJeff0100.00%0
                      Red TeamJesse1416.67%0
                      Red TeamJohn1822.22%0
                      Red TeamJoseph1314.29%0
                      Red TeamJoshua636.36%0
                      Red TeamNicole18%0
                      Red TeamTony2216.22%0
                      Total13218.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

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

                    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:

                        TeamStage 1(calculated field)
                        Red Team18%
                        Blue Team10%

                         

                        Table 2:

                        TeamSalespersonAccount% of sale in stage 1
                        Red TeamJohn55 * 18%
                        Red TeamJack1212*18%
                        Red TeamMary2121*18%
                        Blue TeamLinda3030*10%
                        Blue TeamBrian4141*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