1 Reply Latest reply: Oct 12, 2017 1:11 PM by Vishwarath Nagaraju RSS

    Sum Sales between Dates

    Ben Alexander

      Hi All,

       

      I'm really struggling to sum the sales between two dates and was wondering if anyone could help and explain what I'm doing wrong.

       

      So, I've got 5 fields, Customer, Start Date, End Date, CalendarMonthAndYear, Sales

      *Start Date/EndDates will vary for each customer*

       

      When adding the below calculated dimension, the result is as expected - Yes and No where it should be...

       

      =if([Audit Sales End] > CalendarMonthAndYear and CalendarMonthAndYear >= [Audit Sales Start],'Yes','No')

       

      I replicated this as an expression and changed the 'yes' to sum(sales) & removed 'no', but it's not providing the result I'm after.

       

      I've since tried to adjust this as it appears that if isn't correct, and used something like this instead but it's still not correct

       

      Sum(({<$CalendarMonthAndYear ={">=$([Start Date])<=$([End Date])"}>}, Sales)

       

      Any help would be much appreciated, and it would be good to understand why what I have tried isn't working...

       

      Thanks

       

      Ben