Solved: Re: Average and rolling - Qlik Community

Report Inappropriate Content · ‎2017-01-18

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.

sunny_talwar · ‎2017-02-01

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

View solution in original post

Report Inappropriate Content · ‎2017-01-19

Perhaps try something like:

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

Regards,

Pravesh Dukhi

Report Inappropriate Content · ‎2017-01-19

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

sunny_talwar · ‎2017-01-20

I will check this out today

Report Inappropriate Content · ‎2017-01-22

Hi Sunny,

could you help me with this?

Best regards

Stravan

sunny_talwar · ‎2017-01-22

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‌

Report Inappropriate Content · ‎2017-01-22

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

sunny_talwar · ‎2017-01-22

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

Report Inappropriate Content · ‎2017-01-23

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!

sunny_talwar · ‎2017-01-23

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

Average and Average rolling