1 Reply Latest reply on Oct 9, 2013 11:05 AM by Matt Lutton

    Looking to use MTD Sales measure with filter on Last Date

    Ron McMullen
    Ron McMullen Oct 9, 2013 10:39 AM

      Trying to create a measure to show MTD & YTD sales.

       

      This formula works for MTD Sales:

      IF [DOCDATE] <= TODAY() AND DATEDIFF('month',[DOCDATE], TODAY()) = 0

      THEN [SALESAMT]

      END

       

      BUT, I need it to be based on the last date in the data group.

      Sometimes want to filter data based on Date Range: AS in last July 2013 info

       

      I get an error about mixing aggregated & non-aggregated functions.

       

      When I use the following formula:

      IF [DOCDATE] <= TODAY() AND DATEDIFF('month',[DOCDATE], MAX([DOCDATE])) = 0

      THEN [SALESAMT]

      END

       

      Any Ideas

      • 111 Views
      • Categories: Actions & Filters, Calculations, Data Sources & Connections, Dates & Times
      • Tags:
        • 1. Re: Looking to use MTD Sales measure with filter on Last Date
          Matt Lutton
          Matt Lutton Employee Oct 9, 2013 11:05 AM (in response to Ron McMullen)

          In your original calc, nothing was aggregated so everything went smoothly.  When you added MAX() to the second formula, you aggregated part of the calc, so Tableau wants everything aggregated so it can compute properly.

           

          I have no way to test, but you can try:

           

          IF attr([DOCDATE]) <= TODAY() AND DATEDIFF('month',[DOCDATE], MAX([DOCDATE])) = 0

          THEN sum( [SALESAMT])

          END

           

          I could be missing something else, as well.