Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

1 Solution

Accepted Solutions

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

15 Replies
Not applicable

Re: Average and rolling

Perhaps try something like:


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


Regards,

Pravesh Dukhi

Not applicable

Re: Average and rolling

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

Re: Average and rolling

I will check this out today

Not applicable

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

Not applicable

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

Capture.PNG

Not applicable

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?

Community Browser