15 Replies Latest reply: Feb 1, 2017 10:07 PM by Sunny Talwar

# Average and Average rolling

Thank you inadvance for the help with the following, I have tried to find answers to my questions here and on the web but have not succeeded with everything.

I want to create charts/tables in QlikView (version 11) with the following and I need the formulas for those:

1- "Three-year average for the previous 12 months rolling": ie the average of the past three rolling 12 months.

Tex 201201 should be = (0(200901) +355(201001) +264(201101)) / 3, see the attached Excel file.

2- "The average of the last three years": ie the average number of persons per month for the last three years. e.g. 201202 should be = (15(200902) +8(201002) +25 (201102)) / 3, see the attached Excel file.

• ###### Re: Average and rolling

Perhaps try something like:

rangeavg(after(sum(Sales),0,12))

Regards,

Pravesh Dukhi

• ###### Re: Average and rolling

Thank you, but I don't need after().

• ###### Re: Average and rolling

I will check this out today

• ###### Re: Average and rolling

Hi Sunny,

could you help me with this?

Best regards

Stravan

• ###### Re: Average and rolling

The data you have provided seems to be an extract from QlikView's chart which seems to be already pre-aggregated. To help you best, I would request you to provide the non-aggregated data so that we can see what all you have. It would be best if you are able to share your qvw. If confidentiality is a concern, you can check this out

Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: Average and Average rolling

Thank you Sunny, Now you have two files as attachments.

• ###### Re: Average and Average rolling

Can you check if the attached looks right?

Expression used

(Above(Count(DISTINCT{<PeriodType={'Rolling 12'}>}IDPerson), 12) + Above(Count(DISTINCT{<PeriodType={'Rolling 12'}>}IDPerson), 24))/2

(Above(count(DISTINCT IDPerson), 12) + Above(count(DISTINCT IDPerson), 24))/2

• ###### Re: Average and Average rolling

It works very well and I want to thank you very much for this.

An completed question: How can I get the calculations in those 5 expressions in the script instead of getting them already calculated during the upload of the data? What will be the changes that I have to do in the equations to make them work in the scripts?

Thanks again!

• ###### Re: Average and Average rolling

Sorry I am confused, you want this in the script or do you want them in the front end?

• ###### Re: Average and Average rolling

Sorry I confused you!

It is too heavy for the chart and it takes a long time to be updated after each selection so I thought that maybe it's better to have them calculated already in the script.

• ###### Re: Average and Average rolling

Sorry Sunny if I confused you.

But could you help me with the script equations?

Many thanks in advance!

• ###### Re: Average and Average rolling

I will try to work on this over the weekend.... Probably won't get time to see this before that because of the complexity involved.

• ###### Re: Average and Average rolling

Hi Sunny, hope I'm not interrupting. I wonder if you've had time to look at my question?

Thanks in advance!

• ###### Re: Average and Average rolling

Sorry I totally forgot about this.... checking it again (will have to remind myself what we were doing here)

• ###### Re: Average and Average rolling

Added two new Concatenations to the AsOfPeriodTable table

CONCATENATE (AsOfPeriodTable)

LOAD

Period as AsOfPeriod,

'PREV 1 Rolling 12' as PeriodType,

date(AddYears(addmonths(Period,1-iterno()), -1),'YYYYMM') as Period1,

Year(Period) as Year,

Month(Period) as Month

RESIDENT PeriodTbl

WHILE iterno() <= 12;

CONCATENATE (AsOfPeriodTable)

LOAD

Period as AsOfPeriod,

'PREV 2 Rolling 12' as PeriodType,

date(AddYears(addmonths(Period,1-iterno()), -2),'YYYYMM') as Period1,

Year(Period) as Year,

Month(Period) as Month

RESIDENT PeriodTbl

WHILE iterno() <= 12;

and then the Three-year average for the previous 12 months rolling expression will be this:

(Count(DISTINCT{<PeriodType={'PREV 1 Rolling 12'}>}IDPerson) + Count(DISTINCT{<PeriodType={'PREV 2 Rolling 12'}>}IDPerson))/2

Is this expression slow too?

(Above(count(DISTINCT IDPerson), 12) + Above(count(DISTINCT IDPerson), 24))/2

This seems to be more dynamic and might be kept as is unless you really want to push this to backend also.

Best,

Sunny