Calculating an average in a straight table

[darrellbutler]

[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

[IAMDV · Accepted solution]

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!

[darrellbutler]

DV

Simple eh ? lol

Many thanks this works well.

Darrell.

[IAMDV]

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!

[#5]

Hi

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

Jo