4 Replies Latest reply: Apr 3, 2018 9:11 AM by Sunny Talwar RSS

    Sum accumulated - Pivot Table

    Jesus Martell

      Good Morning


      I need to get a "Neto" field in a pivot table that is the sum of all the values to date, but there is something I can not get out.


      The "Neto" row of a pivot table must contain the sum of everything accumulated up to that "Fecha" (Column), which would indicate how many active users there were at that time. I have tried several sums with Analysis of Sets, but I think that I still do not master it well.


      Can you think of something to get the calculation?


      In principle you should give something as follows:



      I have tried things like these, without a correct result:



      I have tried things like these, without a correct result:

       

      IF (

       

       

      Date ([Subscriptions.DateRegistration])> = DEADLINE_DAY and Date ([Subscriptions.DateRegistration]) <= DATE_HASTA and

       

          ValueList ('High', 'Low', 'Net') = 'Net'

       

      ,

       

                  sum ({$ <'Date' = "{> = $ (= Date ([Subscriptions.DateRegistration]))}">}

       

                        alt ([Subscriptions.Alta])

       

                        -alt ([Subscriptions.Baja])

       

                        -if ([Subscriptions.TypeOperation] = 'terminate', 1.0)

       

                        -if ([Subscriptions.BajaFS] = 1 and ([Subscriptions.GroupPlan] = 'BUNDLE BQ' or [Subscriptions.GroupPlan] = 'REST'), 1.0)

       

                  )

       

          , 0

       

      )

       

       

       

      {$ 2 <Date = "{> = $ (= [Sales.DateShopping])}">}

       

       

       

      / *

       

          Sum (ALL

       

              (

       

               alt ([Subscriptions.Alta])

       

                  -alt ([Subscriptions.Baja])

       

                  -if ([Subscriptions.TypeOperation] = 'terminate', 1.0)

       

                  -if ([Subscriptions.BajaFS] = 1 and ([Subscriptions.GroupPlan] = 'BUNDLE BQ' or [Subscriptions.GroupPlan] = 'REST'), 1.0)

       

              )

       

          )

       

      * /

       

       

       

      // {<[Subscriptions.DateRegistration] = {"<= $ (= before ([Subscriptions.DateRegistration]))"}>}

       

       

       

      / *

       

          ranges (

       

           before (

       

               sum (

       

                    alt ([Subscriptions.Alta])

       

                    -alt ([Subscriptions.Baja])

       

                    -if ([Subscriptions.TypeOperation] = 'terminate', 1.0)

       

                    -if ([Subscriptions.BajaFS] = 1 and ([Subscriptions.GroupPlan] = 'BUNDLE BQ' or [Subscriptions.GroupPlan] = 'REST'), 1.0)

       

                  )

       

                , 0

       

                , ColumnNo ()

       

           )

       

          )

       

      * /