topic Re: Calculate Measure for Current Month Only in QlikView

Calculate Measure for Current Month Only

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Calculate Measure for Current Month Only

sunny_talwar — Fri, 02 Nov 2018 13:34:45 GMT

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

Re: Calculate Measure for Current Month Only

leale1997 — Fri, 02 Nov 2018 13:40:49 GMT

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.

Re: Calculate Measure for Current Month Only

sunny_talwar — Fri, 02 Nov 2018 13:48:54 GMT

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

Re: Calculate Measure for Current Month Only

leale1997 — Fri, 02 Nov 2018 13:52:49 GMT

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

I reloaded the data several times.

Re: Calculate Measure for Current Month Only

leale1997 — Fri, 02 Nov 2018 13:56:30 GMT

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

Re: Calculate Measure for Current Month Only

leale1997 — Fri, 02 Nov 2018 13:57:14 GMT

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

Re: Calculate Measure for Current Month Only

sunny_talwar — Fri, 02 Nov 2018 14:23:55 GMT

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/DateAndTimeFunctions/today.htm