10 Replies Latest reply: Mar 9, 2018 9:33 AM by Colin O'Donnell

# moving average in a Pivot Table

Hello all,

I have two separate tables that I load into Qlik Sense.

I then create a Pivot Table table as follows:

Dim1 is from Table1

Dim2 is from Table 2

Average is Count of Dim1/Count of Dim2

How do I:

1 - create a 3 month moving average based on the Average Values shown.

2 - can this then be put into a chart

Thank you,

Colin

• ###### Re: moving average in a Pivot Table

What would the numbers look like for 3 months moving average?

• ###### Re: moving average in a Pivot Table

Hi Sunny,

It would be a percentage number as well.

Something along the lines of

Count ( {<previous 3 months>} Dim1) / Count ({<previous 3 months>} Dim2)

• ###### Re: moving average in a Pivot Table

May be like this

RangeSum(Above(Count(Dim1), 0, RowNo()))/RangeSum(Above(Count(Dim2), 0, RowNo()))

• ###### Re: moving average in a Pivot Table

Hi Sunny,

No that doesn't work.

if I try either part on its own I get a zero, namely:

RangeSum(Above(Count(Dim1), 0, RowNo()))

or

RangeSum(Above(Count(Dim2), 0, RowNo()))

So when I combine them, it is zero / zero.

• ###### Re: moving average in a Pivot Table

Do I need to join the two tables together first?

Preferably I would prefer to avoid doing so.

• ###### Re: moving average in a Pivot Table

Would you be able to share a sample?

• ###### Re: moving average in a Pivot Table

Hi Sunny,

Yes, no problem.

Attached is an Excel file along with the QVF file in which I have associated the two tables.

I have made the data anonymous and just stripped it down to the bar bones for the purposes of this question.

Thanks,

Colin

• ###### Re: moving average in a Pivot Table

Try this

=RangeSum(Before(Count(Dim2), 0, 3))/RangeSum(Before(Count(Dim1), 0, 3))

• ###### Re: moving average in a Pivot Table

Hi Sunny,

You are legend.

That worked a treat for the Pivot Table.

The next step is to understand "how". I've gleaned the following from a quick search on the Community.:

before([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables. ]]

The full file is here under Functions.txt

Rangesum Above ,Below and Before,After and Range Avg

But that does mean it does not work for Charts.

How can I show it in a Chart please?

Thanks,

Colin

• ###### Re: moving average in a Pivot Table

For the chart, I just swapped "before" for "above" and this worked perfectly.