Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

15 Replies
Not applicable
Author

Perhaps try something like:


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


Regards,

Pravesh Dukhi

Not applicable
Author

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

sunny_talwar

I will check this out today

Not applicable
Author

Hi Sunny,

could you help me with this?

Best regards

Stravan

sunny_talwar

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
Author

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

sunny_talwar

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
Author

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

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