7 Replies Latest reply: Jul 13, 2011 5:03 AM by Stefan Wühl RSS

    help needed - Cummulative

    Gopinathan L

      dear all,

       

      i have transaction table,the data looks like

       

      Type      Month     Amt

      A           Feb         10

      A           Mar         20

      A           Apr         10

      B          Jan          20

      B          Feb          10

      C          Jan          30

      C          Feb          20

      C          Mar          10

       

      if the user selects the month Apr, then i need to show the output as

       

      Type      Month    Cumm_Amt

      A           Apr         40

      B           Apr         30

      C           Apr         60

       

      its working fine if i use the set analysis in the chart.

       

      but wen i do this one in back end, i m getting only Type A for the month of Apr.

      reason is i dont have the transation for B and C in Apr.

      could anyone help me how to resolve this one.

        • help needed - Cummulative
          Stefan Wühl

          Hi gopinathanl,

           

          could you please clarify, what do you mean with "back end"?

           

          Regards,

          Stefan

            • Re: help needed - Cummulative
              Gopinathan L

              Back end means Script part,

               

              in script level,

               

              i done like

               

              if(peek(Type)=Type,peek(Cumm)+Value,Value) as Cumm

                • Re: help needed - Cummulative
                  Stefan Wühl

                  Hi gopinathanl,

                   

                  as a note, you might need to sort your data first after Type, Month to ensure that Type data is consecutive (probably you have already done this).

                   

                  So, as I understand you want either

                   

                  - add a row to the data for each missing month of a type (but up to which month?) with value 0 and correct accumulation. Then you have all data needed for selection.

                   

                  or

                  - include data in your table which is not selected (selected Month= April, but you want to show the last existing month for each type in row Cumm_Amt).

                  At the moment, I think about using set expression again, but this is probably not what you want.

                   

                  What ist the requirement behind your question? To not use set expression as a exercise? Or do you have performance problems if using large amount of data and you want to perform the needed calculation while loading data?

                   

                  Regards,

                  Stefan

                    • Re: help needed - Cummulative
                      Gopinathan L

                      hi stefan,

                       

                      thanks for reply...

                      i want to add a row for each missing month upto today.

                       

                      bcoz wen i do the Cummulative in back end, some of them not having the transaction after certain months.

                      so the cummlative is computed upto that month. wen i select the current month it shows null.bcoz its not having the tranaction for last two or three months.it may have next month (we cant decide).

                       

                      i done it in set analysis but my chart going very slow(bcoz of large amount of data)

                        • Re: help needed - Cummulative
                          Stefan Wühl

                          Hi gopinathanl,

                           

                          please find my proposal for adding the missing rows attached.

                           

                          As you can see in the script, these are the steps I used:

                           

                          1. Create or load the original input data table, plus additional unique TypeMonth

                          2. Create Table with preset values 0 for all Types, all Months

                          3. add rows with zero value to original data for all missing Type / Months combinations (i.e. TypeMonth)

                          4. Do the actual accumulation (your existing code, I added an order by)

                          5. add calendar till today's Month (lookup Month# to Month name)

                           

                          I am just curious, how many rows (Types / Months) do you load, resulting in bad performance using set analysis?

                          I am not sure if my  code is performant, at least it is calculated at load time.

                           

                          Regards,

                          Stefan