Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jmartell
New Contributor

Sum accumulated - Pivot Table

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 ()

     )

    )

* /

4 Replies

Re: Sum accumulated - Pivot Table

Would you be able to share a sample to check it out?

jmartell
New Contributor

Re: Sum accumulated - Pivot Table

View:

neto1.png

Excel, result to do:

neto2.png

jmartell
New Contributor

Re: Sum accumulated - Pivot Table

Let me explain myself a little better:



Suppose "Neto" (Accumulated) until 01/05/2017 had the value of 40,000 (Sum of all "Neto") and that day increased the net by 100, the next day decreased by -50 and the next day increase other 200, which would leave something like this:



04/30/2017,      Neto = 40,000

01/05/2017, 100, Neto = 40,100

02/05/2017, -50, Neto = 40,050

03/05/2017, 200, Neto = 40,250


That is, "Neto" = Neto(day x) + Sum (of all Net (day <x))



Where x = is the "Fecha" (Date) in the columns of the pivot table.

Highlighted

Re: Sum accumulated - Pivot Table

What you are looking for is the accumulated value, right? Check this out

Accumulations

Community Browser