3 Replies Latest reply on Nov 20, 2018 9:37 AM by Pascal Bibeau-Palomo

# Show value for latest date as well as moving average for last 30days together.

Hi All,

I'm stuck with a calculation which seems to be a realistic scenario but not working.

In a table , I want to show value for latest date and in next column , want to show moving average of last 30 days for the same measure.

As soon as I put filter to show value for latest date, the moving average does not work as now it has only the last value available.

Any pointers in this scenario will be much appreciated.

Regards,

Arun

• ###### 1. Re: Show value for latest date as well as moving average for last 30days together.

Right.  when you filter, you eliminate rows.  Your table calcs won't be able to see the eliminated rows for moving averages.

What do you mean by "for the latest date"?

If you are talking about today, then you can do calcs using the function TODAY().

If you are talking about the last date in the data source, you can do this:

{ FIXED : MAX([Date Field]) }

That tells you the biggest value in the data.  Then you can do calcs like:

If [Date Field] = [Fixed Max Date Calc] then [Sales] END

That will load the calc only for rows that have the last date.  SUM([That Calc]) will give you the sum for the last day only, and all your other rows don't have to be stripped out by a filter, and then your moving averages have all the rows available to do what they need to do.

• ###### 2. Re: Show value for latest date as well as moving average for last 30days together.

Hi Arun,

You should use a table calculation to show the latest date. Reason is because table calculations run later than filters in the order of operations so will work better in your case.

So I've created my moving average table calculation, next is to create an ad hoc calculation by double clicking in rows and writing in last() =0. Pressing enter, you will find that only the last value (table calc running table down) says true:

Move this last()=0 field into the filters shelf and set it to True. Then right click on the date field and untick show header (if you'd like to hide the header):

I then re-added the original sum of sales back into the view, so I get this: