
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate a value for the previous month
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
