1 Reply Latest reply: Jun 6, 2016 10:22 PM by Sunny Talwar RSS

    Daily budget & R30 Sales

    James Sample

      Dear Community,

       

      2 questions in one here.

      Q1) I'm trying to get a "Daily" budget figure to compare to my sales value.

      Sum(Budget) / Day(MonthEnd(DateId)) will give me the figure I'm after, but only for the 1st of each month. I need that value in every day, for the applicable months

      Q2) I'd also like a Rolling 30 daily sales average (if possible, in the example data below, utilising the June sales for the July rows that would require it). Accumulate 30 Steps Back won't work because of days with no sales and it won't look back into June.

       

      NOTES:

      1) Because of days with no sales (i.e. missing days), the row count does not lend itself to simple Above() expressions.

      2) Unchecking the "Suppress zero || missing values" checkbox in Presentation tab is BAD because it shows all possible DateId values, despite selections (i.e. displays every day in my Calendar table)

      2) Obviously, there are list box selections elsewhere filtering data, including, but not limited to, a [DateFinYear] field and a [DateMonthName] field which limit the DateId's shown in the straight table chart.

       

      Data looks like this

      DateIdSales Sales R30 Budget
      01/07/20151740761615612
      02/07/20151902130
      03/07/20151085310
      06/07/2015163390
      07/07/2015736420
      08/07/2015773970
      09/07/2015475860
      10/07/2015421570
      13/07/2015205920
      14/07/2015288100
      15/07/20151003660
      16/07/2015510630
      17/07/20151965070
      20/07/20151256200
      21/07/2015426330
      22/07/2015701500
      23/07/2015781220
      24/07/2015481710
      27/07/2015280740
      28/07/2015237680
      29/07/2015411310
      30/07/2015560700
      31/07/2015357390
      01/08/201501798036
      03/08/20151327130
      04/08/2015177460
      05/08/2015762950
      06/08/20151902130
      07/08/2015463280
      10/08/2015207780
      11/08/2015253680
      12/08/2015522150
      13/08/20151057670
      14/08/2015421980
      17/08/2015237290
      18/08/2015462840
      19/08/2015483060
      20/08/2015968690
      21/08/20151062740
      24/08/2015138900
      25/08/2015503610
      26/08/2015515930
      27/08/2015739500
      28/08/2015758840
      31/08/20151733260
      01/09/2015804871681866
      02/09/20151000990
      03/09/2015566170
      04/09/2015834260
      07/09/2015333220
      08/09/2015575500
      09/09/2015601400
      10/09/20151222120
      11/09/20151459840
      14/09/2015479720
      15/09/2015324740
      16/09/2015325050
      17/09/2015972860
      18/09/2015549850
      21/09/2015215780
      22/09/2015471540
      23/09/20151067490
      24/09/2015233210
      25/09/20151296870
      28/09/2015190410
      29/09/2015383070
      30/09/20151066100