How to compute number of minutes from current time
2 April, 2012
Hi:
I've been trying to figure out a way to compute the number of minutes from the current time. I have noted the 'Days From Current Date' Pre-Defined Formula and wanted to try and modify it to compute the number of minutes from the current date. It would appear that simply changing the d to an m does not actually produce the desired result when I create a Calculated Function in my View design (see below):
DATEDIFF(m, "Latest Vehicle Information"."EventDate", GETDATE())
Are there any suggestions as to what I may be doing incorrectly or what I might want to look at to make this work?
I've been trying to figure out a way to compute the number of minutes from the current time. I have noted the 'Days From Current Date' Pre-Defined Formula and wanted to try and modify it to compute the number of minutes from the current date. It would appear that simply changing the d to an m does not actually produce the desired result when I create a Calculated Function in my View design (see below):
DATEDIFF(m, "Latest Vehicle Information"."EventDate", GETDATE())
Are there any suggestions as to what I may be doing incorrectly or what I might want to look at to make this work?
Hi Chis,
If you are working with MS SQL Server use the formula below to calculate number of minutes from the current date:
DATEDIFF(mi, "Latest Vehicle Information"."EventDate", GETDATE())
If you are working with HSQL Server use:
DATEDIFF('mi', "Latest Vehicle Information"."EventDate", CURDATE())
Please, note, if your view is optimised you have to make "Latest Vehicle Information" table MANDATORY.
- Jenny.
If you are working with MS SQL Server use the formula below to calculate number of minutes from the current date:
DATEDIFF(mi, "Latest Vehicle Information"."EventDate", GETDATE())
If you are working with HSQL Server use:
DATEDIFF('mi', "Latest Vehicle Information"."EventDate", CURDATE())
Please, note, if your view is optimised you have to make "Latest Vehicle Information" table MANDATORY.
- Jenny.
Baah... FACEPALM. Sorry Jenny, I was working on this late and realized my mistake. Thank you for pointing out my MS SQL error. :)
I am sooo close to having the report I need here and I've come across one more stumbling block.
My ultimate goal... I have a table that provides the date of the last time a field unit communicated with our back end server. I have included a calculated field in the View that computes the number of minutes between 'now' and the last time the unit reported in (thanks Jenny, the column is computed from the above query and works perfectly).
I want the user to be able to see this and know if units in the field have not reported in the last XX minutes today. I wanted to provide two filters on the dataset:
1. A 'today' filter that just grabs all timestamps equal to 'today' from the EventDate column.
2. A filter that is based on a parameter and will allow the user (using a slider) to adjust 'how many minutes old' I want to filter on. This would allow the user to, say, adjust the slider so he is only seeing units that haven't reported in more than 60 minutes, or perhaps more than 10 minutes. This allows us the flexibility to show certain criteria based on a company's individual reporting rate - some may have units that only report in every hour, some have units that report in every couple of minutes. In the calculated field, I CAST the number of minutes into an INT, thinking it would be easier to use an input parameter with an INT by having the user simply enter a numeric value for the number of minutes (or use the slider to select the number of minutes).
My problem:
I have the calculated 'Minutes Since Last Report' field in my report list, however, YF will not allow me to use that field as part of a filter.
Could someone please enlighten me as to:
a. How to use my 'Minutes Since Last Report' calculated field (in combination with a numeric parameter) to filter values in a table?
Much thanks in advance.
Regards,
Chris
I am sooo close to having the report I need here and I've come across one more stumbling block.
My ultimate goal... I have a table that provides the date of the last time a field unit communicated with our back end server. I have included a calculated field in the View that computes the number of minutes between 'now' and the last time the unit reported in (thanks Jenny, the column is computed from the above query and works perfectly).
I want the user to be able to see this and know if units in the field have not reported in the last XX minutes today. I wanted to provide two filters on the dataset:
1. A 'today' filter that just grabs all timestamps equal to 'today' from the EventDate column.
2. A filter that is based on a parameter and will allow the user (using a slider) to adjust 'how many minutes old' I want to filter on. This would allow the user to, say, adjust the slider so he is only seeing units that haven't reported in more than 60 minutes, or perhaps more than 10 minutes. This allows us the flexibility to show certain criteria based on a company's individual reporting rate - some may have units that only report in every hour, some have units that report in every couple of minutes. In the calculated field, I CAST the number of minutes into an INT, thinking it would be easier to use an input parameter with an INT by having the user simply enter a numeric value for the number of minutes (or use the slider to select the number of minutes).
My problem:
I have the calculated 'Minutes Since Last Report' field in my report list, however, YF will not allow me to use that field as part of a filter.
Could someone please enlighten me as to:
a. How to use my 'Minutes Since Last Report' calculated field (in combination with a numeric parameter) to filter values in a table?
Much thanks in advance.
Regards,
Chris
Hi Chis,
There are different ways to solve your problem. One of them is as follows:
Shutdown your Yellowfin Tomcat Server.
Go to the directory on your computer where you installed your Yellowfin and locate custom-functions.xml file in WEB-INF folder. The path to the file might look like this one:
C:Program FilesYellowfin 6appserverwebappsROOTWEB-INFcustom-functions.xml
Open this custom-functions.xml file with either Notepad or Notepad ++
Find SQL Server functions section:
Add the following function:
Minutes From Current Date, SQL Server
1
Date
datetime
DATEDIFF(mi, $1, GETDATE())
DATEDIFF(mi, $1, GETDATE())
SQL
numeric
Save your changes and restart Yellowfin Tomcat Server.
When Yellowfin is up and running again, please go to your View.
Proceed to Step2. On the left side of your View Builder locate Calculated Fields ->Formula Templates folder. In the list of available pre-built formulas locate your �Minutes from sysdate, SqlServer� formula. Drag and drop it to your Available Fields. Go to Formula Tab. Click on �Date� link:
Select �EventDate� as your Field for Date value to be used in your formula. Apply changes; Activate.
Save your view.
Now you can get rid of your old calculated metric that was created using Freehand SQL. You can use the new metric in your report and you can use this metric as a filter as well. Convert that filter into slider and specify MIN and MAX values for your slider (MIN=0; MAX=1440 � minutes in a day).
You can create another custom function that can select date component from a timestamp value and use it as a filter to filter out current date (TODAY).
-Jenny.
There are different ways to solve your problem. One of them is as follows:
Shutdown your Yellowfin Tomcat Server.
Go to the directory on your computer where you installed your Yellowfin and locate custom-functions.xml file in WEB-INF folder. The path to the file might look like this one:
C:Program FilesYellowfin 6appserverwebappsROOTWEB-INFcustom-functions.xml
Open this custom-functions.xml file with either Notepad or Notepad ++
Find SQL Server functions section:
Add the following function:
Minutes From Current Date, SQL Server
1
Date
datetime
DATEDIFF(mi, $1, GETDATE())
DATEDIFF(mi, $1, GETDATE())
SQL
numeric
Save your changes and restart Yellowfin Tomcat Server.
When Yellowfin is up and running again, please go to your View.
Proceed to Step2. On the left side of your View Builder locate Calculated Fields ->Formula Templates folder. In the list of available pre-built formulas locate your �Minutes from sysdate, SqlServer� formula. Drag and drop it to your Available Fields. Go to Formula Tab. Click on �Date� link:
Select �EventDate� as your Field for Date value to be used in your formula. Apply changes; Activate.
Save your view.
Now you can get rid of your old calculated metric that was created using Freehand SQL. You can use the new metric in your report and you can use this metric as a filter as well. Convert that filter into slider and specify MIN and MAX values for your slider (MIN=0; MAX=1440 � minutes in a day).
You can create another custom function that can select date component from a timestamp value and use it as a filter to filter out current date (TODAY).
-Jenny.
Thank you, thank you, thank you. Your responses are always filled with fantastic detail. I'll give it a shot tomorrow when I'm back in the office and let you know how I get on.
Cheers!
Cheers!
Hey Jenny,
Thank you for your assistance in creating the custom function. Minor hiccup in that it was looking for SQLServer and not SQL but that wasn't a big deal.
That being said, I think I've come across a possible bug. While the newly created function works like a charm, the use of a parameter to feed it does not. Namely, I have my database returning the unit ID and the number of minutes from the current time (as discussed above), with no filters, everything works like a charm. I introduced an initial filter to grab everything from 'Today' (which filters on simply my date field) and confirmed it is, indeed, returning all values from today. I then introduced a second filter that acts on my 'Minutes from Current Date' field and instructed it to act on my numeric parameter called 'Minutes Since Last Vehicle Report' (I used the 'Greater Than Column' operator).
My sample set has two records from today - one with the 'Minutes from current' date field currently at 572 minutes and the second record with 87 minutes. Regardless of what I set my parameter at (whether I put it to 60 minutes or 90 minutes), both records are returned (obviously, I would expect that putting the parameter to 90 minutes, the second record wouldn't show up).
Conversely, if I merely change my parameter field to a user entered value (ie. changing my filter operator to 'Greater than' instead of 'Greater than column') and enter in a value of 90, the second record disappears.
Bottom line, it would seem that the parameter is not being fed properly to the calculated field. I have attached a few images to show you the setup.
Thank you for your assistance in creating the custom function. Minor hiccup in that it was looking for SQLServer and not SQL but that wasn't a big deal.
That being said, I think I've come across a possible bug. While the newly created function works like a charm, the use of a parameter to feed it does not. Namely, I have my database returning the unit ID and the number of minutes from the current time (as discussed above), with no filters, everything works like a charm. I introduced an initial filter to grab everything from 'Today' (which filters on simply my date field) and confirmed it is, indeed, returning all values from today. I then introduced a second filter that acts on my 'Minutes from Current Date' field and instructed it to act on my numeric parameter called 'Minutes Since Last Vehicle Report' (I used the 'Greater Than Column' operator).
My sample set has two records from today - one with the 'Minutes from current' date field currently at 572 minutes and the second record with 87 minutes. Regardless of what I set my parameter at (whether I put it to 60 minutes or 90 minutes), both records are returned (obviously, I would expect that putting the parameter to 90 minutes, the second record wouldn't show up).
Conversely, if I merely change my parameter field to a user entered value (ie. changing my filter operator to 'Greater than' instead of 'Greater than column') and enter in a value of 90, the second record disappears.
Bottom line, it would seem that the parameter is not being fed properly to the calculated field. I have attached a few images to show you the setup.
Minor update. I have this working now simply by displaying my filter as a slider (ie. not using a parameter at all). The use of the parameter input to the filter, however, still does seem to be a problem.
Cheers.
Cheers.
Hi Chris,
That is what I advised you in my initial post � to use �Minutes since last report time� pre-built metric as a filter and convert it into a slider (or use a user prompt box).
I do not quite understand why you decided to use �Greater than Column� operator. It just doesn�t make sense to me. According to your task it is definitely should be a �Greater than� operator.
And why did you try to use Parameter as a filter? Parameter is used as a filter only if you want to pass your Parameter value into your report calculated field, which in your case is not needed at all.
If you still have any problems with tuning your report or getting correct results, please, let me know. I'll be more than happy to help you.
-Jenny.
That is what I advised you in my initial post � to use �Minutes since last report time� pre-built metric as a filter and convert it into a slider (or use a user prompt box).
I do not quite understand why you decided to use �Greater than Column� operator. It just doesn�t make sense to me. According to your task it is definitely should be a �Greater than� operator.
And why did you try to use Parameter as a filter? Parameter is used as a filter only if you want to pass your Parameter value into your report calculated field, which in your case is not needed at all.
If you still have any problems with tuning your report or getting correct results, please, let me know. I'll be more than happy to help you.
-Jenny.
Hi Chris,
Here is the example on how you can use your Parameter in a filter section.
Let�s say you have an Invoiced Amount in Australian Dollars and you want to give your users an ability to choose an exchange rate, so that they can see this Invoiced Amount in their currency.
Here is the original report:
Add a Parameter 1 to your Fiter
Create a calculated field like this:
If a user wants to see his Invoiced amount in US dollars and he knows that 1 AUD =1.0346 USD then by entering the value 1.0346 into a Parameter box that value will be passed by Yellowfin into a calculated field and report will look like the one below:
Of course, you can rename Parameter 1 and call it �Exchange rate� for example.
Hope my explanation helps.
-Jenny.
Here is the example on how you can use your Parameter in a filter section.
Let�s say you have an Invoiced Amount in Australian Dollars and you want to give your users an ability to choose an exchange rate, so that they can see this Invoiced Amount in their currency.
Here is the original report:
Add a Parameter 1 to your Fiter
Create a calculated field like this:
If a user wants to see his Invoiced amount in US dollars and he knows that 1 AUD =1.0346 USD then by entering the value 1.0346 into a Parameter box that value will be passed by Yellowfin into a calculated field and report will look like the one below:
Of course, you can rename Parameter 1 and call it �Exchange rate� for example.
Hope my explanation helps.
-Jenny.
Apologies, Jenny. Chalk it up to my rookie use of Yellow Fin. I'm getting better, but obviously I am far from knowing much. I know enough to be ignorant and dangerous!! My initial thought was the use of the parameter allowed me to use the slider function, but as you said, you did note it in your post that I could use it as a filter (sorry, I had 'parameter' on the brain and skipped over the 'use it as a filter' part).
In any event, thanks again as you have certainly furthered my knowledge of your fantastic product.
...you are the master. :)
Cheers
In any event, thanks again as you have certainly furthered my knowledge of your fantastic product.
...you are the master. :)
Cheers