Calculating formula with past values but ignoring them (dimensions) in Chart

falko_thom — Tue, 31 May 2016 06:15:23 GMT

Hello community,

please take a look at the empeded qvw.

I have to calculate weekyears and weekdays averages of a daily amount of past values.

Second I need to take these values as prediction for the same weekday in future weeks.

As an example the calculated average for all weekdays 2 in week 2 is 2,175 (upper table).

This value has to be shown in the lower table which shall only show future weeks. Is this possible?

I used the following:

=Avg(TOTAL <d_weekday, d_week>
  Aggr(
  Sum(amount)
  , d_year, d_week, d_weekday)
)

Which produces the following table:

Unfortunately I am not able to show only future weeks - that would be week 2 in 2017.

What am I doing wrong?

I'd be glad for any help.

Thanks, Falko Thom

Re: Calculating formula with past values but ignoring them (dimensions) in Chart

sunny_talwar — Tue, 31 May 2016 18:05:00 GMT

May be this:

=If(d_date >= Today(), Avg(TOTAL <d_weekday, d_week> Aggr(

Sum(amount)

, d_year, d_week, d_weekday))

)

falko_thom — Tue, 07 Jun 2016 06:29:11 GMT

Thanks a lot. That kinda helped me very much.

I always tried to find a solution using a dynamic dimension. But that's far better.