2 Replies Latest reply on Feb 9, 2018 2:24 PM by Jonathan Drummey

    Using an independent date to total amounts.

    James Charlwood

      Does anyone know of a way to to use a date independent of those in my data to total transactions effective on a particular day/month.

       

      What I am trying to achieve is across the x axis the list of sale dates. Along the y axis would be the refund month. So if in Jan 17 we made 5 sales of £10,000, January 17 would total £50,000. If in March 17 we refunded £1,000 off one of these sales we would remove the whole of the £10,000 sale from our total, and in March we would now show £40,000

       

      I can do this on a month by month basis using the below, but I don't want to have to write this for every month.

       

      IF [Sale Month]>0

      AND [Refund Month]<1

      THEN

      [Amount]

      ELSE 0

      END

       

      What this should do is provide a cumulative sum of all sales by month, but exclude the value of all sales where a refund has been made. The result being our exposure to refunds.

       

      Hope this makes sense and you're able to help.