Count by records created before current month

[martin_hamilton]

Hi - using sense I wish to use a KPI to show a calculation which counts records created before the current MonthName (where the field name is SUBS_CONNECTED_MONTH)

My expression works fine when selecting dates equal to todays Month Name:

Count({<SUB_CONNECTED_MONTH={"$(vCurrentMonth)"}>}Distinct SUBS_NUMBER)

Where I have a variable of :

vCurrentMonth:

=MonthName(Today(1))

I have tried to use a not equal value of <> instead of = but still cant seem to get it to work?

Any ideas or suggestions?

thanks

Martin

[martin_hamilton]

Thanks Sunny!

[timmarsh]

Here's how I did it.

=count({<MonthYear-={"$(vThisMonthYear)"}>} DISTINCT FIELDNAME)

I wanted to count unique instances, and yes, I could create a resident table and load distinct, with a counter. This is a small data set so easy to sanity check.

vThisMonthYear is defined by:

let thisMonthYear=makedate(year($(vMaxDate)),month($(vMaxDate)));
Let vThisMonthYear=date(MonthStart(thisMonthYear),'MMM-YYYY');

There's probably a more optimal/efficient way to do this, but works for me.