4 Replies Latest reply: Jul 15, 2016 1:02 AM by Jayaseelan K RSS

    Calculate a value for the previous month

    Kaitlyn Ahearn

      Hi there, I'm looking for some help calculating the number of sign ups for a product in the previous month (previous month to today's month).

       

      Using a list of dates in a "ContractStartDate" field, I want to know the count of Customer_SiteIDStatus='SIGN UP' that occurred in the previous month.

       

      Any ideas? Thank you

        • Re: Calculate a value for the previous month
          Damian Eralio

          So, you want the count of Customer_SiteIDStatus = 'SIGN UP' where Month(ContractStartDate) = Month and Year of the same day during the previous month

           

          Does your data span years? because this would make a difference

          • Re: Calculate a value for the previous month
            Damian Eralio

            You can use the above() function if you're working with a chart. See the bottom of the attached file.

            Or

            If you are working with simple numbers you can use text boxes. Without editing the script we can use set analysis + if statement to get the previous month count off the date:
            count({$<ID={'IN'}>}if(num(Month(Date))=num(Month(Addmonths(Today(),-1))) and Year(Date) = Year(Addmonths(Today(),-1)),ID))

             

            both methods can be seen in the attached document

             

            But I really do not think using IF statements is best practice, because you'll need to be careful about selections. For example, if you made a choice on date that would eliminate last month's dates, your equation would show 0, because all the dates for the previous month would not be considered.

             

            Preferrably, you would be able to edit the script and use a calendar in there to mark the previous month sales. This would allow you to ALWAYS display the previous month sales and ignore any date selections so the user never sees a 0 or any untrue value. I can follow up with more information on this if you're able to edit the script.

            • Re: Calculate a value for the previous month
              Jayaseelan K

              Hi Kaitlyna,

                      Try this,

               

              Count({<ContractStartDate={">=$(=Date(MonthStart(Max(ContractStartDate),-1),'M/D/YYYY'))<=$(=Date(MonthEnd(Max(ContractStartDate),-1),'M/D/YYYY'))"},Customer_SiteIDStatus='SIGN UP'>}Customer_SiteIDStatus)