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

Accumulating in pivot Table considering not selected dates in filters

Anonymous — Fri, 28 Apr 2017 12:42:39 GMT

Hi folks,

I have the following scenario:

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:

in instead of this (what I want):

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

Thanks in advance.

Rubens

sunny_talwar — Fri, 28 Apr 2017 12:45:02 GMT

Try this

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

Anonymous — Fri, 28 Apr 2017 12:57:54 GMT

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 — Fri, 28 Apr 2017 13:01:18 GMT

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)

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

Anonymous — Fri, 28 Apr 2017 13:23:52 GMT

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:

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

Tks mate!

sunny_talwar — Fri, 28 Apr 2017 13:42:20 GMT

Looks like it is working...

Are you not seeing this?