Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ()
)
)
* /
Would you be able to share a sample to check it out?
View:
Excel, result to do:
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.
What you are looking for is the accumulated value, right? Check this out