topic Comparison of equivalent periods in App Development

Comparison of equivalent periods

HunterWatts — Sat, 16 Nov 2024 01:42:22 GMT

Dear experts,
First of all, I apologize if my grammar is not ideal, I am Argentine and I am using the google translator to write the query.
I work in a health company transporting patients, and I have to make a comparison between the services performed between the previous month and the current one, taking into account the beginning of the month, the date selected in the current month and the beginning of the previous month and the date equivalent to the one selected but from the previous month. That part I could solve this way.

Measurement that counts the services until the selected date:

count ({<Product = {"Emergency"}, CalledDate = {"> = $ (= max (Date (MonthStart (CalledDate)), 'DD / MM / YYYY')) <= $ (= max (Date (CalledDate) , 'DD / MM / YYYY'))) "}>} IncidentNumber)

Measure that counts the services up to the same date but the previous month:

count ({<Product = {"Emergency"}, CalledDate = {"> = $ (= max (Date (MonthStart (AddMonths (CalledDate, -1))), 'DD / MM / YYYY')) <= $ ( = max (Date (AddMonths (CalledDate, -1), 'DD / MM / YYYY'))) "}>} IncidentNumber)

My problem has to do with the end of the month. since if I am on the 30th and the previous month has 31 days, I lose that last day in the analysis.
So I am trying to do that if the selected day corresponds to the end of the month, it takes me all the days of the previous month.

I have been trying to do this with no results.

count ({<Product = {"Emergency"}, CalledDate = {$ (= "if (FLOOR (date (GetFieldSelections (CalledDate), 'DD / MM / YYYY')) = FLOOR (date (MonthEnd (GetFieldSelections (CalledDate) ), 'DD / MM / YYYY')),> = max (Date (MonthStart (AddMonths (CalledDate, -1))), 'DD / MM / YYYY') <= max (Date (MonthEnd (AddMonths (CalledDate, -1), 'DD / MM / YYYY'))),> = max (Date (MonthStart (AddMonths (CalledDate, -1))), 'DD / MM / YYYY')) <= max (Date (AddMonths ( CalledDate, -1), 'DD / MM / YYYY')) ")}>} IncidentNumber)

But this would not be working correctly, since it does not bring me any value.
I appreciate any help you can offer me to solve it

Re: Comparison of equivalent periods

sunny_talwar — Tue, 11 Aug 2020 11:12:24 GMT

Try this

Count({<Product = {"Emergency"}, CalledDate = {">=$(=Date(MonthStart(Max(CalledDate), -1), 'DD/MM/YYYY'))<=$(=Date(If(Day(Max(CalledDate)) = Day(MonthEnd(Max(CalledDate))), Floor(MonthEnd(Max(CalledDate), -1)), AddMonths(Max(CalledDate), -1)), 'DD/MM/YYYY'))"}>} IncidentNumber)

Re: Comparison of equivalent periods

HunterWatts — Tue, 11 Aug 2020 11:32:00 GMT

Thank you very much for answering, This solves a part, but I need to put it in an if inside a setanalysis so that when for example I select April 30, it will calculate until March 31, but if I select any date that does not coincide with the end of the month, it will calculate until the same day of the previous month.

Re: Comparison of equivalent periods

sunny_talwar — Tue, 11 Aug 2020 12:21:48 GMT

Right and that is what the expression should do... isn't that is what it is doing?

Re: Comparison of equivalent periods

HunterWatts — Tue, 11 Aug 2020 13:00:25 GMT

You're right, I hadn't seen well. It works perfectly. Thank you very much.