13 Replies Latest reply: May 18, 2012 6:26 PM by leonardompt RSS

    Change sum value

      Hi guys,


      I'm new in this forum and didn't find any answers about my problem.

       

      My pivot table has an expression (Valor_total) that sum 3 diferent variables (VALOR_1, VALOR_2, VALOR_3). I have to modify manually some of these sum's(not each variable), there is no specific rules. What can I do to input this value in the sum expression?

       

      Tks,
      Leonardo

        • Change sum value

          Can you provide more information? You can declare variables in QV and then set the value in for the variable via an input box. However, the variable value would be applied to all rows in the dataset.

            • Change sum value

              But I want to change some of this values, not all of them. Eg.

               

              NAME       VALOR_TOTAL (V1+V2+V3)

               

              XXX            3000
              YYY            2000
              ZZZ            1500
              AAA            500

               


              I wanna change the sum of AAA to 550  but keep the other values, and I don't wanna change V1,V2 and V3.

                • Change sum value

                  I thought that was what you desired but I wanted to make sure. Are you trying to permanently adjust the value or run what if scenarios? The only thing I can think of off the top of my head is to load the adjustments either via an inline load or via an externally maintained data source (text file or Excel file). With these, you adjustments would be applied everytime you reloaded the QVW.

                    • Change sum value

                      is it possible? i don't want to change the original values, only the sum expression. can i make it via inline load? how do I do this?

                        • Re: Change sum value

                          Here is the example. The level at which you track the data (i.e. if you wanted to track adjustments for a name and date) can be increased. The real limiting factor is how many rows you need to adjust and the fact that it is a manual process. It might be easier to track the data in Excel so that others could help maintain it and you are not constantly editing the script in the QVW.

                            • Change sum value

                              Yeah, almost that. but I want this sum table with all values of the original table, and i dont want this 'incremental adjustment' , but the "full adjustment". i want to type on this "adjustment table" the exact value that will be showed on the Pivot table. Can you get it? Sorry my English, I'm from Brazil, My english is not fully efective. hahahah

                                • Change sum value

                                  I will modify 3 or 4 rows only. I can do this manually, no problem.

                                  • Re: Change sum value

                                    I don't think I understand? Do you want the adjusted value to be 550? If that is the case, you can just change the value in the inline "Adjustment" table to be 550 and simplify the expression to be sum(Adjustment).

                                    • Re: Change sum value

                                      I re-read your last post. I don't think you want to type the exact value. If you do this, you will have to enter a value for each row, regardless of whether it needs to be adjusted or not. The method I used sums up the existing data and then adds in your adjustment amount. This way, you only need to add data to the inline table for the rows that need to be adjusted. Using this method will also allow you to track the rows you have adjusted.

                                        • Change sum value

                                          I got you point.  But all I need is this table you sent me, the Sum Table should be like this:

                                           

                                          NAME               SUM
                                          AAA                    550
                                          XXX                     3000

                                          ZZZ                      2000
                                          YYY                       1500

                                           


                                          and table adjustment with

                                           

                                          AAA           550

                                           


                                          got it?

                                           

                                          I'm not able to get this incremental adjustment, I only have the full value

                                            • Re: Change sum value

                                              I think I get it. How about this? I did not rename the tables but there are three now: 1 with the original value; 1 with the original value and the adjusted value; 1 with only the adjusted value. I am heading home for the day. I think you have what you need to continue. You probably just need to experiment with how you want to display the data. The formulas for all the fields are in the Straight Table objects. You can alter the formulas as you see fit. Regards.

                                                • Change sum value
                                                  Yeah, this way. Only that should be diferent
                                                  Adjustments:
                                                  load * inline [
                                                  Name, Adjustment
                                                  AAA, 50]
                                                  ;

                                                  should be
                                                  Adjustments:
                                                  load * inline [
                                                  Name, Adjustment
                                                  AAA, 550]
                                                  ;
                                                  And This 550 being a inputbox, and shown on the Sum Table as "550".

                                                   

                                                  Its something like that

                                                   

                                                  AAA = SUM (ADJUSTMENT)
                                                  THE REST OF VARIABLES = V1+V2+V3

                                                   

                                                  Got it?

                                                   

                                                  This 550 i'll input manually. This 50 should not exists