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

      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

        • 1. Re: Looking to use MTD Sales measure with filter on Last Date
          Matt Lutton

          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.