Calculate a value for the previous month

[#1]

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

[deec]

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

[#3]

Hi Damian,

Yes the data spans multiple years.

I want the count of Customer_SiteIDStatus = 'SIGN UP' for the entire previous month. So if it's July right now, I'm looking to get the count of the sign ups for all of June.

Thanks!

[deec]

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.

[jayaseelan]

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)