Print Page | Close Window

Get active equipment count using a date parameter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23010
Printed Date: 03 May 2024 at 4:56pm


Topic: Get active equipment count using a date parameter
Posted By: Tonyak74
Subject: Get active equipment count using a date parameter
Date Posted: 19 Jul 2022 at 10:52am
I currently have a report that I run with 2 date prompts a starting date and an ending date prompt where I select a dd/mm/yyyy for each.

In this report, I have a formula that is counting active equipment records at the end of the reporting period.

if (IsNull({Equipment.CreatedOn}) Or Date({Equipment.CreatedOn}) <= {?Ending Date}) AND
    Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Date({Equipment.RetireDate}) <= {?Ending Date}))
then 1
else 0

I then sum this formula and it gives me the correct count.

I am working on a new report that I do not want to have a beginning and ending date parameter, I only want to have a Year parameter.

I am breaking down the counts of equipment that were added each quarter.
I have this part working.

I would like to get the total Active count at the end of the period to be based on the Year parameter.

If I were to run the report for the current year, I am able to easily report active as of today/currentdate.

What I am hoping to achieve is that if I run the report for a previous year like 2020, I will get the count of equipment as of 12/31/2020.

If my prompt is only asking YYYY, how can I get it to show the last day of the year???

Sorry for the very long explanation, hope it makes sense.


I think I was able to get it figured out. I just changed my original formula a little bit to:

if (IsNull({Equipment.CreatedOn}) Or Month({Equipment.CreatedOn})<=12 and Day({Equipment.CreatedOn}) <=31 and Year({Equipment.CreatedOn}) <= {?Year})
AND
Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Year({Equipment.RetireDate}) <= {?Year}))
then 1
else 0

Seems to be working. I will do more testing.


Replies:
Posted By: hilfy
Date Posted: 23 Aug 2022 at 7:23am
You can simplify the formula. Because all months will <= 12 and all days will be <= 31, you don't have to check for that. So, changing your formula to this will work and will decrease the amount of processing required: 

if (IsNull({Equipment.CreatedOn}) Or Year({Equipment.CreatedOn}) <= {?Year})

AND

Not ({Equipment.Inactive} and (IsNull({Equipment.RetireDate}) Or Year({Equipment.RetireDate}) <= {?Year}))

then 1

else 0


-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window