Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have to make a pivot table with this structure example: I use in dimensions Account, Year(Date1), Month(Date1)
2010 | 2011
-----------------------------------------------------------------------------------------------------------------------------------------
Jan Feb Mar | Jan Mar
-----------------------------------------------------------------------------------------------------------------------------------------
Sal | Acum Sal | Acum Sal | Acum Sal | Acum Sal | Acum
Account1 100 100 50 150 20 170 35 205 45 250
Account2 100 100 50 150 20 170 35 205 45 250
I need the accumulation from first month on first year.
i Tried with:
Rangesum(Before(Total sum(sal),0,Columnno(Total)))
But when i select one month (pe Feb) and one year(pe 2010) i have this:
2010
--------------------------------------------
Feb
----------------------------------------------
Sal | Acum
Account1 50 50
Account2 50 50
Instead of:
2010
--------------------------------------------
Feb
----------------------------------------------
Sal | Acum
Account1 50 150
Account2 50 150
i Tried then with:
Rangesum(Before(Total sum({<Date1=>}sal),0,Columnno(Total)))
And the result , when i choose feb 2010 is:
2010 | 2011
-----------------------------------------------------------------------------------------------------------------------------------------
Jan Feb Mar | Jan Mar
-----------------------------------------------------------------------------------------------------------------------------------------
Sal | Acum Sal | Acum Sal | Acum Sal | Acum Sal | Acum
Account1 - 100 50 150 - 170 - 205 - 250
Account2 - 100 50 150 - 170 - 205 - 250
Anybody can help me?, please
Thanks
This is most likely best solved in the load script using the peek function to calculate the Acum values. Here's a very simple example.
I tried also, but i have problem with null values
2010 | 2011
-----------------------------------------------------------------------------------------------------------------------------------------
Jan Feb Mar | Jan Mar
-----------------------------------------------------------------------------------------------------------------------------------------
Sal | Acum Sal | Acum Sal | Acum Sal | Acum Sal | Acum
Account1 100 100 50 150 - - 35 35 45 80
Account2 - - 50 50 20 70 35 105 45 150
Please help me