topic Rolling issue in QlikView

Rolling issue

Tue, 20 Mar 2012 15:15:36 GMT

I have attached an example of QVW that illustrates the problem that I have.

INTRODUCTION:

In summary, the example contains just 2 tables:

- MasterCalendar

- Jobs file

The 2 are linked by a date that I call OrderDate

The "Jobs" file has a number of fields but the ones of interest are:

- No - this is the job number (this is not unique as there can be a number of entries for the same "job"

- Amount - This is the amount charged for each job

We have data spaning 4 years

REQUIREMENT:

We need to show both monthly totals (as in the example) and 12-month rolling averages against time. Just to clarify, for each month we need to show the average of the 12 previous months as at that time (e.g. for Jan 2011 it should show the average of Feb 2010 through to Jan 2011). This needs to be broken down by location as shown in my example for the totals.

The location dimension appears to be confusing QlikView. Any pointers would be highly appreciated.

Thanx in advance

Alexis

Re: Rolling issue

Tue, 20 Mar 2012 15:31:33 GMT

Try an expression like this?

Rangeavg( Above (count({$<Year =, Month =, OrderDate =>} DISTINCT [No]),0,12))

I am not sure if I completly get what you are looking for. The {$<Year =, Month =, OrderDate =>} will make it so selections to date wont affect the chart so it will show correctly. (IE It needs all data for months to show correct avg.)

Rolling issue

Anonymous — Tue, 20 Mar 2012 15:39:50 GMT

You mean link for field JobDate not for OrderDate or i missing something?

Rolling issue

Tue, 20 Mar 2012 15:52:59 GMT

Apologies Alfasierra - I renamed some of the field names to make it easier to understand.. yes you are right - the link field is "JobDate".

Rolling issue

Tue, 20 Mar 2012 15:59:37 GMT

Hi Marc,

Thanx for responding.

I would like to show the entire timeline (Jan 2007 - now) broken by month (as in the example attached) - for the "rolling" part this should be previous 12 months (if available).

For example:

For March 2007 for example it will be the average of Jan 2007 - March 2007 (only 3 values are available)

For Nov 2007 for example it will be the average of Jan 2007 - Nov 2007 (only 11 values are available)

For Mar 2008 for example it will be the average of Apr 2007 - Mar 2008 (last 12 values)

For Jan 2012 for example it will be the average of Feb 2011 - Jan 2012 (last 12 values)

etc..

Rolling issue

Tue, 20 Mar 2012 16:02:20 GMT

Correct that should be what

Rangeavg( Above (count({$<Year =, Month =, OrderDate =>} DISTINCT [No]),0,12))

is doing.

It takes the distinct count of jobs per dimension, then it averages the previous 11 (12 including the current month)

Rolling issue

Tue, 20 Mar 2012 16:13:59 GMT

Tried the rangeavg approach and I don't seem to be getting the "separation" that I expect. For a start I am not getting rolling values by location (see chart below) - it seems that "rolling" does not like more than one dimension to be present in the chart.

Rolling issue

Anonymous — Tue, 20 Mar 2012 16:21:22 GMT

Alexis did u try this?

Sum({< Year = , Month = , JobDate={">=$(=AddMonths(MonthStart(Today()), -12))<=$(=(AddMonths(Today(), 0)))"} >} Amount)

Rolling issue

Tue, 20 Mar 2012 16:33:52 GMT

alfasierra wrote:
Alexis did u try this?
Sum({< Year = , Month = , JobDate={">=$(=AddMonths(MonthStart(Today()), -12))<=$(=(AddMonths(Today(), 0)))"} >} Amount)

Yes I did Alfasierra and getting the same behaviour - I know my document is a little on the large size but have you downloaded to see the problem that I am reporting?

Thanx again

Alexis

Re: Rolling issue

Tue, 20 Mar 2012 17:28:44 GMT

Do you have a set number of locations? I know the Rangeavg and Above functions don't seem to like more than 1 dimension. (Could try total?)

If the number of locations will not change you could create an expression for each. then use set analysis to limit each expression to the correct location. That should make it so the avg only flows for each location

Rolling issue

Wed, 21 Mar 2012 16:10:44 GMT

Thank you very much Marcsliving.

Although the hard-coded locations is less than ideal it has given me something to work on.

If anyone can get around the problem relating to Rangeavg and multiple dimensions let me know..

Thanx again.

Alexis

Rolling issue

Wed, 21 Mar 2012 16:14:26 GMT

Keep an eye one this one, they seem to be having a similar issue with multiple dimensions Re: Previous value in Full Accumulation