3 Replies Latest reply on Aug 30, 2018 5:16 AM by suresh.gooty

    Date calculation help needed

    Somok Sarkar

      Dear All,

      I have been trying to achive two calculations where I would require some help.

      1. Previous month sales.

      2. Current Year YTD Sales

      The objective here is to show this values in the tooltip of the net sales as shown in the dashboard. I have removed the calculations and updating a fresh twbx file.

       

      For eg If I select the order date end range as 01-Mar-2018 and start range as April 2017 then the tool tip should consider

      1. Previous month sales = sales only in February 2018

      2. Current Year YTD sales = sales from 01-Jan-2018 to 01-Mar-2018

       

      Regards,

        • 1. Re: Date calculation help needed
          Somok Sarkar

          did anyone tried to find a solution for this?

          • 2. Re: Date calculation help needed
            Tim Dines

            I cannot open your attached file so here is a general idea.  For the previous month you will want DATEPART('month',[endDate]))-1.  The YTD is a little more complicated but you should be able to find an example by searching "Working with Dates".  If you plan to display both of these values on the same axis, you will need to look up "Two date ranges on the same axis".  There are good examples available on all of these.

            • 3. Re: Date calculation help needed
              suresh.gooty

              your date range is start date and end date.... now for previous month sales you need to go to previous month that means start date -30. or month number -1 so that you go to last month and display sales.

               

              if month(date)>=month(end date) - 1 then

              sales

              end...

              for january you need to write special logic...

               

              this is for previous month sales.

               

              if date>"1-1-" + year(date) and date<end date then

              sales

              end... this is for ytd sales.... you may extend the logic little bit to get exact. of course you have many online readymade solutions for this query...