Calculate Measure for Current Month Only

[leale1997]

I want to count activity based on a field called 'DateClosed' and calculate only records with a 'DateClosed' in the current month.  I need a Distinct Count based on a field called 'RecordID'.  I had a formula in place that was working well until the month changed from October to November.  I reloaded the data set to insure it wasn't a refresh issue.

This is the formula:

=Count({<DateClosed = {"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"},

Project = {'Deviation', 'UIR','OOS','EMIR'},

NCCriticality = {'Minor'} >} DISTINCT RecordID)

Is there a more innovative and/or flexible way to complete this requirement?

[sunny_talwar]

So, what was your expectation? Would you still want to see RecordID count for the month of October?

[leale1997]

Hi Sunny!

I would like the formula to change as the calendar month changes without any manual assistance.

So right now it should be calculating activity for November 1st and 2nd.  As the calendar moves forward the formula will include the rest of the days in November.  On December 1st the formula should change to calculate only activity for December.  So on December 1st it will change to calculate only activity for that one day than it will grow as the activity for December grows.  In January it will go back to the first day of data and accumulate as the dates move forward.

[sunny_talwar]

I would assume that this is what you expression should already be doing, isn't it?

[leale1997]

No.  For some reason it is still calculating October.  It is not flipping to November. 

I reloaded the data several times.

[leale1997]

Interesting.  I closed the file and reopened it and now it flipped to November calculations.

[leale1997]

Is the 'distinct' in the correct place for that formula.  I want to count only distinct RecordID's

[sunny_talwar]

Try using this

=Count({<DateClosed = {"$(='>=' & Date(MonthStart(Today(1))) & '<=' & Date(MonthEnd(Today(1))))"},

Project = {'Deviation', 'UIR','OOS','EMIR'},

NCCriticality = {'Minor'} >} DISTINCT RecordID)

Read about timer_mode here

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...