Formulae: Counting records by month?

MattBoxall · June 10, 2014 4:41AM

Hi all,

I'm currently trying to put together a formulae field that will calculate the number of new cases opened (using a field with various dates noted) within the previous calander month. the difference is the core Excel data will be updated monthly and I want to have this automated rather than having to change the formulae field every month to reference a specific date.

I've tried using DATEADD and then an IF but I can't get recommended fomulae to work as GETDATE is not recognised. Any ideas how I can achieve this in Omniscope?

thanks,

Matt

paola · June 10, 2014 5:16AM

You could set the process in the DataManager to update your report automatically.

Duplicate the [Date] field, creating [Month] field, isolating the MMM part of your date (alternatively MMM-yy if your dataset runs over longer period) .
If you now wish to count number of items in the target field use:
SUBSET_NONEMPTYCOUNT([Text], SUBSET([Month]))
will create a value for each [Month].

ALTERNATIVE:
If your requirement is to populate the new formula field with only number of cases in the previous month (rather than have different values for each month), you can achieve this dynamic by branching out the records to de-duplicate the [Month] field and apply the
RANK([Month]) formula.

Now the latest month will always have value 1, and the previous month value 2 etc.
You can merge the rank values block with the main dataset on [Month] and apply formula
SUBSET_NONEMPTYCOUNT([Text], SUBSET([Rank month],2,"="))

Please post a sample dataset if this doesn't answer your question fully.

MattBoxall · June 10, 2014 6:32AM

Works perfectly, thanks!

Matt

Welcome!

Categories

Ideas Parade

Tagged