Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
falko_thom
New Contributor III

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

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.

d_week2
d_weekday01234
14802314217524582006

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:

d_year201220132014201520162017
d_week222222
d_weekday01234012340123401234023401234
14802314217524582006148023142175245820061480231421752458200614802314217524582006148021752458200614802314217524582006

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

1 Solution

Accepted Solutions

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

May be this:

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

  Sum(amount)

  , d_year, d_week, d_weekday))

)


Capture.PNG

View solution in original post

2 Replies

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

May be this:

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

  Sum(amount)

  , d_year, d_week, d_weekday))

)


Capture.PNG

View solution in original post

falko_thom
New Contributor III

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

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.