Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Course Of The Month
7 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Crystal formula or Command Date Buckets
Posted on 2010-11-22
trying to setup are report using some date fields to create some date buckets
So I have a start date and a end date as fields.
I want to create a command or use formulas(prefer) to calculate the dates between those start and end dates.
bucket 1is enddate - startdate = time duration1 x .025, then add this to startdate
bucket 2 is enddate - startdate = time duration2 x .063 , then add this to startdate
bucket 3 is enddate - startdate = time duration3 x .0125 , then add this to startdate
All Dates are actual numbers and I end up converting them over to dates to view them
Anyone have suggestion on how to do this?
So I have a start date and a end date as fields.
I want to create a command or use formulas(prefer) to calculate the dates between those start and end dates.
bucket 1is enddate - startdate = time duration1 x .025, then add this to startdate
bucket 2 is enddate - startdate = time duration2 x .063 , then add this to startdate
bucket 3 is enddate - startdate = time duration3 x .0125 , then add this to startdate
All Dates are actual numbers and I end up converting them over to dates to view them
Anyone have suggestion on how to do this?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
6
2
14 Comments
Active 1 day ago
I am not clear what you are trying to do.
You can use DateDiff to subtract dates with you choice of days, weeks, hours, etc
You can then use DatsAdd to add the units back
mlmcc
You can use DateDiff to subtract dates with you choice of days, weeks, hours, etc
You can then use DatsAdd to add the units back
mlmcc
OK so I did this and it works!! :)
Anyway i now believe i need to put it all into one formula, as Im trying to get this into a crosstab scenario..
this is what I want to do.. is it possible in one formula to have multiple "buckets" or results? Im getting a "Boolean is required" or" ) is missing" etc. errors..
if ((ESPs_ARSToLocalDate ({@lts - ops}* .025) + {projectdesc.opsStart}))
then 'milestone1'
else if ((ESPs_ARSToLocalDate (({@lts - ops}* .088) + {projectdesc.opsStart})
then 'milestone2'
else '0'
Anyway i now believe i need to put it all into one formula, as Im trying to get this into a crosstab scenario..
this is what I want to do.. is it possible in one formula to have multiple "buckets" or results? Im getting a "Boolean is required" or" ) is missing" etc. errors..
if ((ESPs_ARSToLocalDate ({@lts - ops}* .025) + {projectdesc.opsStart}))
then 'milestone1'
else if ((ESPs_ARSToLocalDate (({@lts - ops}* .088) + {projectdesc.opsStart})
then 'milestone2'
else '0'
Active 1 day ago
The value of the test must be true or false
((ESPs_ARSToLocalDate ({@lts - ops}* .025) + {projectdesc.opsStart}))
This results in a number
The second if is missing some )
II am still not clear what you are trying to do.
Can you give an example with data as to what goes in which bucket?
Yes, you can use a formula like you are trying to produce.
mlmcc
((ESPs_ARSToLocalDate ({@lts - ops}* .025) + {projectdesc.opsStart}))
This results in a number
The second if is missing some )
II am still not clear what you are trying to do.
Can you give an example with data as to what goes in which bucket?
Yes, you can use a formula like you are trying to produce.
mlmcc
im trying to create buckets of time for projects its the differences between a start date and end date of each project..
the dates are a unix time stamp or something and im trying to increment up from beginning.. .025 .088. .095 etc etc
I have 9 buckets that each have the increment and they work. My thought was to put it into one formula as the end result is to put it into a crosstab , otherwise it would be "stacked" 9 times over and I wouldnt get the result I need, so again, I wanted to put it in one formula so i could crosstab it out.
make sense?
the dates are a unix time stamp or something and im trying to increment up from beginning.. .025 .088. .095 etc etc
I have 9 buckets that each have the increment and they work. My thought was to put it into one formula as the end result is to put it into a crosstab , otherwise it would be "stacked" 9 times over and I wouldnt get the result I need, so again, I wanted to put it in one formula so i could crosstab it out.
make sense?
Active 1 day ago
Something like
If enddate - startdate = time duration1 x .025 then
"bucket 1"
else if enddate - startdate = time duration2 x .063 then
"bucket 2"
else ETC
mlmcc
If enddate - startdate = time duration1 x .025 then
"bucket 1"
else if enddate - startdate = time duration2 x .063 then
"bucket 2"
else ETC
mlmcc
yes thats what i did.. and where im stuck.. its telling me "a boolean is required" on bucket 2 portion
if ESPs_ARSToLocalDate (({@lts - ops}* 1.002) + {projectdesc.opsStart})
then 'milestone1'
else if ESPs_ARSToLocalDate (({@lts - ops}* 1.003) + {projectdesc.opsStart})
then 'milestone2'
else '0'
then 'milestone1'
else if ESPs_ARSToLocalDate (({@lts - ops}* 1.003) + {projectdesc.opsStart})
then 'milestone2'
else '0'
Active 1 day ago
Yours
if ESPs_ARSToLocalDate (({@lts - ops}* 1.002) + {projectdesc.opsStart})
then 'milestone1'
Mine
If enddate - startdate = time duration1 x .025 then
"bucket 1"
Mine has the = to compare enddate - startdate to the time duration
Yours simply adds 2 numbers but doesn't compare it to anything
mlmcc
if ESPs_ARSToLocalDate (({@lts - ops}* 1.002) + {projectdesc.opsStart})
then 'milestone1'
Mine
If enddate - startdate = time duration1 x .025 then
"bucket 1"
Mine has the = to compare enddate - startdate to the time duration
Yours simply adds 2 numbers but doesn't compare it to anything
mlmcc
Active today
I'm guessing that ESPs_ARSToLocalDate converts the calculated value to a date, so what you're ending up with is something like:
if #12/01/2010#
then 'milestone1'
else if #12/02/2010#
then 'milestone2'
else '0'
You have to do some kind of test on those dates (compare them with another date or check the month or something). Just saying "if date" makes no sense to CR. That's what mlmcc was getting at.
James
if #12/01/2010#
then 'milestone1'
else if #12/02/2010#
then 'milestone2'
else '0'
You have to do some kind of test on those dates (compare them with another date or check the month or something). Just saying "if date" makes no sense to CR. That's what mlmcc was getting at.
James
hmm.. maybe this cant be done then. not sure what dates I would be comparing it too.. (itself maybe?)
the ultimate goal is to get them in a pivot table thats why I was working to put them in one formula.
the ultimate goal is to get them in a pivot table thats why I was working to put them in one formula.
Active today
Maybe you can't.
Looking back at your original and follow-up posts, you've said that you're trying to use start date and end date fields to define "buckets" and that you're trying to create a cross-tab. So, presumably you want to use the difference between the dates in some way to define the columns or rows in a cross-tab. In general, you could do that, in various ways.
However, it looks like you're trying to create multiple "buckets" for each record. You can't do that. CR can only include each record in one group. Going back to your original post:
bucket 1is enddate - startdate = time duration1 x .025, then add this to startdate
bucket 2 is enddate - startdate = time duration2 x .063 , then add this to startdate
bucket 3 is enddate - startdate = time duration3 x .0125 , then add this to startdate
You have nothing there that says "use the first formula for these records, the second formula for those records, etc.". Those formulas would return 3 separate values for each record, like you're trying to include each record in all 3 "buckets", which you can't do.
If you're not actually trying to include each record in multiple "buckets", can you try again to explain exactly what you're trying to do?
FWIW, going back to mlmcc's suggestion, he was assuming that "time duration1", "time duration2" and "time duration3" were fields or formulas in your report, so, for example, (enddate - startdate = time duration1 x .025) would be testing to see if (enddate - startdate) was equal to (time duration1 x .025). In that case, you could have one formula that produced different values depending on whether (enddate - startdate) was equal to (time duration1 x .025), or (time duration2 x .063), or (time duration3 x .0125). You could use that formula for the columns or rows in a cross-tab.
However, I'm assuming that "time durationX" was referring to the result of (enddate - startdate), so (enddate - startdate = time duration1 x .025) really meant ((enddate - startdate) x .025). In that case, you've got different 3 different formulas producing different values for the same record.
James
Looking back at your original and follow-up posts, you've said that you're trying to use start date and end date fields to define "buckets" and that you're trying to create a cross-tab. So, presumably you want to use the difference between the dates in some way to define the columns or rows in a cross-tab. In general, you could do that, in various ways.
However, it looks like you're trying to create multiple "buckets" for each record. You can't do that. CR can only include each record in one group. Going back to your original post:
bucket 1is enddate - startdate = time duration1 x .025, then add this to startdate
bucket 2 is enddate - startdate = time duration2 x .063 , then add this to startdate
bucket 3 is enddate - startdate = time duration3 x .0125 , then add this to startdate
You have nothing there that says "use the first formula for these records, the second formula for those records, etc.". Those formulas would return 3 separate values for each record, like you're trying to include each record in all 3 "buckets", which you can't do.
If you're not actually trying to include each record in multiple "buckets", can you try again to explain exactly what you're trying to do?
FWIW, going back to mlmcc's suggestion, he was assuming that "time duration1", "time duration2" and "time duration3" were fields or formulas in your report, so, for example, (enddate - startdate = time duration1 x .025) would be testing to see if (enddate - startdate) was equal to (time duration1 x .025). In that case, you could have one formula that produced different values depending on whether (enddate - startdate) was equal to (time duration1 x .025), or (time duration2 x .063), or (time duration3 x .0125). You could use that formula for the columns or rows in a cross-tab.
However, I'm assuming that "time durationX" was referring to the result of (enddate - startdate), so (enddate - startdate = time duration1 x .025) really meant ((enddate - startdate) x .025). In that case, you've got different 3 different formulas producing different values for the same record.
James
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples).
Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone,
Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8.
We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month7 days, 12 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.