topic Last 30 day average in QlikView

Last 30 day average

nesawant — Mon, 29 Aug 2016 09:10:12 GMT

Hi,

I am trying to create a pivot in like:

Date	Count(Activity)	Last 30 day average
29-Aug-2016	Count(Activity) on 29th Aug	Count(Activity) for last 30 days/30
28-Aug-2016	Count(Activity) on 28th Aug	Count(Activity) for last 30 days/30

& so on...

I am unable to get last 30 day average right. The problem is due to Date being used as dimension.

If I make a calendar object & select a date & use any other dimension, the calculation turns out to be right.

Can someone help?

Best Regards,

Neha

Re: Last 30 day average

sunny_talwar — Mon, 29 Aug 2016 12:29:42 GMT

You can use Above/Below functions with RangeAvg().

Missing Manual - Above() and Below()

or Before/After if the Dates are pivoted

Missing Manual - Before() and After()

Re: Last 30 day average

Mon, 29 Aug 2016 12:52:09 GMT

Hi Neha,

There are basically 2 ways:

1. pls add an indicator in your Calender/fact table each of the last 30 days. like:

if(today() - Fact-Date > 30, 0,1) as 30DayAvg_Ind now you marked your records which are valid for the AVG calculartion

now you can calculate the proper average with a formula somewhat like:

=AVG(${ <30DayAvg_Ind={'1'} >} Counter), this wil do the trick in a formula.

Calculate the averages of all your dimensions in the script with the group by statement:

and add these averages to your facts with join or applymap...

like:

30DayAVG: NoConcatenate

resident Facts

Key,

avg(Counter) as Average_Dim1

where 30DayAvg_Ind group by dimension1; (this should be done for every dimension)

and add these averages to all the facts records with a left join or applymap

Than you are able to display the created average field (Average_Dim1)from your datamodel into your table

good luck and HTH

Re: Last 30 day average

nesawant — Tue, 30 Aug 2016 14:10:06 GMT

Hi,

This pivot is not being calculated for all dates. I have a calendar object which is used to select a date & the pivot needs to be calculated only for that date.