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

Accumulating in pivot Table considering not selected dates in filters

Hi folks,

I have the following scenario:

  • Pivot table using accumulating concept: It works!

Accumulation.png

  • What am I trying?
    • When selecting 2016 and 2017 periods, I want to consider 2015 in the calculation in order to have the correct accumulated amount. \however, I'm getting this:

Accumulation2.png

in instead of this (what I want):

Accumulation3.png

As I see it, it might be a piece of cake for you experts.

Thanks in advance.

Rubens

5 Replies
sunny_talwar

Try this

RangeSum(Before(Sum({<Year>}Value), 0, ColumnNo())) * Avg(1)

Anonymous
Not applicable
Author

Great Sunny,

Another question:

  • In my case, I want to ommit 2015 in a dimension level. However, I still want to see the the accumulated amountt considering 2015. Should I iterate on a dimension level?

Tks,

Rubens

sunny_talwar

May be like this

RangeSum(Before(Sum({<Year>}Value), 0, ColumnNo())) * Avg({<Year = {'2016', '2017'}>}1)

or if you still want Year selections to be honored

RangeSum(Before(Sum({<Year>}Value), 0, ColumnNo())) * Avg({<Year *= {'2016', '2017'}>}1)

If you want the 2016 and 2017 to be dynamic

RangeSum(Before(Sum({<Year>}Value), 0, ColumnNo())) * Avg({<Year = {$(=Max(Year)), $(=Max(Year)-1)}>}1)

or

RangeSum(Before(Sum({<Year>}Value), 0, ColumnNo())) * Avg({<Year = {$(=Year(Today())), $(=Year(Today())-1)}>}1)

Anonymous
Not applicable
Author

The problem with this logic is 2015 is still being shown in the chart. Furthermore, I have to freeze how many years I want to consider in the chatt.

I need something like this:

Accumulation4.png

Accumulation2.png

I need a chart like above, but accumulating from the past no selected periods.

Tks mate!

sunny_talwar

Looks like it is working...

Capture.PNG

Are you not seeing this?