2 Replies Latest reply on Jun 20, 2018 2:31 AM by Mavis Liu

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

    arun.yadav

      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.
          Joe Oppelt

          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.
            Mavis Liu

            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:

             

            2018-06-20_10h24_32.png

             

            2018-06-20_10h24_39.png

             

            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):

             

            2018-06-20_10h24_48.png

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

             

            2018-06-20_10h30_10.png

             

            Please see attached workbook.

             

            Thanks,

             

            Mavis