Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rrodriguesrr
New Contributor III

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

Re: Accumulating in pivot Table considering not selected dates in filters

Try this

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

rrodriguesrr
New Contributor III

Re: Accumulating in pivot Table considering not selected dates in filters

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

Re: Accumulating in pivot Table considering not selected dates in filters

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)

rrodriguesrr
New Contributor III

Re: Accumulating in pivot Table considering not selected dates in filters

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!

Re: Accumulating in pivot Table considering not selected dates in filters

Looks like it is working...

Capture.PNG

Are you not seeing this?

Community Browser