2 Replies Latest reply on Mar 10, 2016 7:55 PM by Venkata Dantuluri

# Table calculation and LOD

HI all,

I am using the superstore datasheet with the '2014' replaced by '2016' .

The requirement is..

Average of the  sum of sales of the previous 3 weeks. This is to be achieved for the last 3 weeks.

If we are in week 0 then...

Week(going backwards) --   sum(sales)

1                                        --   10

2                                       --     15

3                                        --      20

4                                      --       5

5                                      --      15

6                                      --      10

What we need to achieve is

Week      --   Window_avg of sales

1       --     13.33 = (15+20+5)/3

2      --      13.33 =(20+5+15)/3

3      --     10 = (5+15+10)

We can achieve this for all the previous weeks. However, to show only the last weeks, I used the C.F...

[ last 3 weeks]=[Order Date]>(DATETRUNC('week',today)-22)  in the filter and selected TRUE.... but that impacts the result set, expectedly.

Some of you might suggest me to hide the weeks beyond the 3rd week, but I am looking for an alternate solution.

Foll are the C.Fs that I have used.....

Window_avg of sales=window_avg(sum(sales),-3,-1) and

last 3 weeks=[Order Date]>(DATETRUNC('week',today)-22) which is placed in the filter.

PLease check the attached twbx file for a better picture.

Thanks,

Raju

• ###### 1. Re: Table calculation and LOD

Hello Venkata!

I think all we need to do is just modify the formula used for the [last 3 weeks] filter to use this:

LOOKUP(MIN([Order Date]), 0)>(DATETRUNC('week',TODAY())-22)

By filtering with a table calc (the LOOKUP function) we are able to "filter" out the rows but still allow the WINDOW_AVG calc to reference them.  Table calc filters act more like hiding the rows instead of completely filtering them from the view.

How does this look?

Thanks!

1 of 1 people found this helpful
• ###### 2. Re: Table calculation and LOD

HI Dan,

Thank you very much.

Just 1 more thing... can we also have the no: of reds and greens for each row.

Thanks again.