topic Calculating an average in a straight table in QlikView

Calculating an average in a straight table

darrellbutler — Wed, 13 Apr 2011 09:17:30 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0636.example.xls:550:0]

Hi,

I would like to replicate the above task from Excel into Qlikview.

The data in grey already exists in my datamodel and can be viewed in my straight table, the issues i'm facing are:

How do I insert future dates that don't exist in the source database, and

What formula would I use to work out the forecast production ?

The forecast is based on an average of the production dates 6,8 & 10 days ago.

I've been using a combination of range average and above functions but I'm struggling to come up with an average for a non continous date range.

Kind Rgds

Darrell

Calculating an average in a straight table

IAMDV — Wed, 13 Apr 2011 10:38:19 GMT

Hi Darrell,

Do you have the QV document with sample data? That would be really helpful to answer your question. However you can achieve this ask by using Set Analysis Expression.

Here is the idea...

Declare 3 variables something like this..

vSixDayBack : (Avg({$<[Production Date] = ([Production Date] - 6)>} [Units Produced]))

vEightDayBack : (Avg({$<[Production Date] = ([Production Date] - 8)>} [Units Produced]))

vTenDayBack : (Avg({$<[Production Date] = ([Production Date] - 10)>} [Units Produced]))

Then use the RangeAvg function and call the 3 variables...

RangeAvg($(vSixDayBack), $(vEightDayBack), $(vTenDayBack))

Hope this helps!

Calculating an average in a straight table

darrellbutler — Wed, 13 Apr 2011 11:30:30 GMT

Simple eh ? lol

Many thanks this works well.

Darrell.

Calculating an average in a straight table

IAMDV — Wed, 13 Apr 2011 11:42:11 GMT

Hi Darrell,

Yeah! straight forward but not very simple. I am learning a lot by answering the posts on this forum. Can't believe its just 6 weeks since I started using QV. Unbelievable isn't it!!!

Cheers!

Re: Calculating an average in a straight table

Tue, 15 Jan 2013 10:31:24 GMT

Im trying to use this myself

let

vDayBackOne= Avg(Num{$<[FinancialWeek] = ([FinancialWeek] - 1)>},[SalesRetailGross]);

in my script but Im getting an error in my script arouns the dollar sign and curly bracket - Can you see whats wrong with it at all?

Thanks