topic Re: Average and rolling in QlikView

Average and Average rolling

Wed, 18 Jan 2017 17:34:31 GMT

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

Thu, 19 Jan 2017 10:03:05 GMT

Perhaps try something like:

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

Regards,

Pravesh Dukhi

Re: Average and rolling

Thu, 19 Jan 2017 10:51:59 GMT

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

Re: Average and rolling

sunny_talwar — Fri, 20 Jan 2017 11:55:11 GMT

I will check this out today

Re: Average and rolling

Sun, 22 Jan 2017 18:29:58 GMT

Hi Sunny,

could you help me with this?

Best regards

Stravan

Re: Average and rolling

sunny_talwar — Sun, 22 Jan 2017 18:42:27 GMT

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

Sun, 22 Jan 2017 20:40:42 GMT

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

Re: Average and Average rolling

sunny_talwar — Mon, 23 Jan 2017 01:50:22 GMT

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

Mon, 23 Jan 2017 21:47:56 GMT

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

sunny_talwar — Mon, 23 Jan 2017 21:50:28 GMT

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

Re: Average and Average rolling

Mon, 23 Jan 2017 22:55:48 GMT

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

Tue, 24 Jan 2017 20:49:31 GMT

Sorry Sunny if I confused you.

But could you help me with the script equations?

Many thanks in advance!

Re: Average and Average rolling

sunny_talwar — Wed, 25 Jan 2017 02:17:35 GMT

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

Wed, 01 Feb 2017 17:15:42 GMT

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

sunny_talwar — Thu, 02 Feb 2017 02:50:45 GMT

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

Re: Average and Average rolling

sunny_talwar — Thu, 02 Feb 2017 03:07:37 GMT

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