Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Accumulation in pivot table with 2 or more dimensions

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

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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