7 Replies Latest reply: Aug 9, 2011 4:30 AM by yomarcos2 RSS

    sum function not working correctly

      Hello,

       

      I am currently having a problem with the sum function.  My revenue for a particular quarter should be for ex: 300M, instead it is around 200B.  My expression for this is

      sum({<[# of FQs from current FQ]={0,'-1','-4'}>}[TOTAL REVENUE ($)])

       

      I also have graphs summing the total # of billed sales, and they are double or quadruple counting as well, so it must be a problem with my load script?  Same formula i just replaced [TOTAL REVENUE ($)] with [TOTAL BILLED SALES]

       

      Furthermore, I have a simple chart that was able to correctly calculate the revenue for each quarter a few days ago, and now is summing just like my charts.

       

       

       

       

       

       

       

       

       

      At the same time, from a completely different load file and script I am summing my revenue by product sold, and further my revenue should be summed across 8 quarters with each quarter summing to a different amount, in the form of a pivot table.  Instead three of the quarters will have the exact same revenue down to the dollar, then the next 5 will all have the same revenue.  My expression is as follows;

       

      sum({$<[# of FQs from current FQ]={0,'-1','-2','-3','-4','-5','-6','-7','-8','-9','-10'}>}[TOTAL REVENUE ($RL)])

       

       

       

       

       

      I realize that my fields are very similar, but they are different, notice the RL, and the excel file that each one loads from has them correctly labeled.

      Thanks so much for all your help,

      Alex

        • sum function not working correctly

          Alex,

           

          Could you upload an example of your data?

           

          Jay

          • sum function not working correctly

            The data is not loading the decimal places correctly.  Ex:  Revenue that should sum to $1,444,639 in excel is being summed in qlikview as $144,463,924.  So the qlikview data is being summed 2 decimal places too much.

             

            Another example is the excel sum is $1,065,221 and then in qlikview its $106,522101.

             

            The number format in qlikview is set to $, and its the same in excel

            • sum function not working correctly

              Good morning, I am trying to sum all the values that match one specific condition but I can sort it out. If first I show all the values I want to sum it is correct:

               

              EventId       RISK REVENUE  AMOUNT    1

              76672         $25,131.50

              176674       $25,131.50

              176671       $31,600.00

              176673       $31,600.00

               

              I used the dimension EventId and this expression:

              = IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

                                            (ProjectedRevenue)

              )

               

               

              but when I use the sum function it prints a result doesnt make any sense cos it doesnt show the total that should be $113.463:

               

              EventId  RISK REVENUE  AMOUNT

              176672  $1,759,205.00

              176671  $2,212,000.00

              176674  $3,518,410.00

              176673  $4,424,000.00

               

              I used the dimension EventId and this expression (the same than before but with the sum function at the begining:

              = sum(IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

                                            (ProjectedRevenue)

              )

              )

               

              I didnt selected the option "Full accumulation" in Expressions tab.

              Could you please tell me what I am doing wrong?

              Thanks a lot in advance.