How to count days between today and most recent recorded date

#1

Hello.

I’m trying to do a countdown of days since the last accident onsite. I know that I’d need to subtract the days since today and the most recent recorded date, but I have no idea of how to put it into a formula.

1 Like
#2

Hello @gerardo.cebollero,

I did a video that covers very much what you are describing.
https://www.dundas.com/learning/extend-your-data-with-dundasscript

enjoy
-jeff

1 Like
#3

Thank you Jeff. I have a further question. How can I make sure the formula utilizes the most recent date? As in, I don’t necessarily need to just get an aging, but rather, need the formula to do the calculation by subtracting the difference from the most recent incident.

#4

Hi Gerardo,

If you need the most recent date, just create a metricset that sorts your dates descending and perhaps select only the top 1 (both are options in the metric set menu). If you’re trying to take a full table and subtract the difference from the most recent incident across many columns, you might want to bake this information into a datacube or try a formula metric set from two existing metric sets.

If you’re unfamiliar with anything that I described above, please let me know and i’ll point you to some reading material.

cheers.

#5

@jeff I definitely need the reading material. Thanks for your help!

#6

These should help.

Sorting - to put your most recent date at the top
https://www.dundas.com/Support/learning/documentation/data-metrics/metric-set-analysis-tools#h4-sorting-and-filtering

Once you have the dates you want at the top of your metric set - limit the records return since you probably don’t want all of them. https://www.dundas.com/support/learning/documentation/analyze-data/displaying-top/bottom-records

Also useful: Formula visualization for combining data. Depending on your exact need, you might want this as it’s very handy.
https://www.dundas.com/support/learning/documentation/analyze-data/formulas/adding-formulas#h5-formula-visualizations

#7

The next version introduces Data Tokens, that will enable the user to query data for any primitive values like dates, strings or numeric values. Such a token will let you query your table of choice for the last used date.

#8

Hello Jeff. Would the aging query work if the data was uploaded from an excel log? I’m doing the dashboard from a log for now until we can get the process automated with IT. But I’ve been trying to get the query running and always get these errors:

Data retrieval failed, as it was not possible to generate a valid query for the specified metric set.

  • (Dundas.BI.Data.DataRetrieval.InvalidQueryException)*
    8
    Process
    Dundas.BI.Core, Version=2.0.0.0, Culture=neutral, PublicKeyToken=521861cc52c056d5
    Dundas.BI.Data.DataRetrieval.ReaderProcessor
    Void Process()
  • at Dundas.BI.Data.DataRetrieval.ReaderProcessor.Process()*
  • at Dundas.BI.Data.DataRetrieval.PlanManager.GetDataCubeResult()*
  • at Dundas.BI.Data.DataRetrieval.PlanManager.GetResultCellset()*
  • at Dundas.BI.Data.DataRetrieval.GroupRequest.<>c__DisplayClass45_0.b__0()*
  • at Dundas.BI.Services.CallerContextService.<>c__DisplayClass25_0`1.b__0(Object state)*

Version: 5.0.1.1005
Runtime error Object reference not set to an instance of an object. Line: 2 Column: 56

  • (Dundas.BI.Scripting.ScriptException)*
    8
    Throw
    Dundas.BI.Core, Version=2.0.0.0, Culture=neutral, PublicKeyToken=521861cc52c056d5
    Dundas.BI.Scripting.ScriptEngineData
    Void Throw(Dundas.BI.Scripting.ScriptException)
  • at Dundas.BI.Scripting.ScriptEngineData.Throw(ScriptException scriptException)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.OperatorNode.Run(Object inputObj)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.MemberAccessNode.Run(Object inputObj)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.ReturnNode.Run(Object inputObj)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.CodeBlockNode.Run(Object inputObj)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
  • at Dundas.BI.Scripting.ScriptEngine.RunScript()*
  • at Dundas.BI.Scripting.SuperScript.RunScript(SuperScriptState state)*
  • at Dundas.BI.Data.Transforms.ScriptProcessor.ExecuteScript(ScriptElement element, StepInfo step, Boolean isFinalResult, SuperScriptState scriptState)*
  • at Dundas.BI.Data.Transforms.ScriptProcessor.ComputeScriptStep(ScriptElement element, Int32 stepIndex, SuperScriptState scriptState)*
  • at Dundas.BI.Data.Transforms.ScriptProcessor.ComputeElement(ScriptElement element)*
  • at Dundas.BI.Data.Transforms.ScriptProcessor.EvaluateOutputExpressions()*
  • at Dundas.BI.Data.Transforms.StandardTransforms.CalculatedElementTransform.Read()*
  • at Dundas.BI.Data.DataRetrieval.TransformReader.Read()*
  • at Dundas.BI.Data.Transforms.Transform.Read()*
  • at Dundas.BI.Data.DataRetrieval.TransformReader.Read()*
  • at Dundas.BI.Data.DataRetrieval.ReaderProcessor.ConvertMeasures(ITransformReader transformReader)*
  • at Dundas.BI.Data.DataRetrieval.ReaderProcessor.Process()*
    Object reference not set to an instance of an object.
  • (System.NullReferenceException)*
    8
    GetType
    mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
    System.Object
    System.Type GetType()
  • at System.Object.GetType()*
  • at Dundas.BI.Scripting.PropertyNode.Run(Object inputObj)*
  • at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)*
#9

Hi @gerardo.cebollero - in the case of this error message, please reach out to support@dundas.com. As far as i can see, there should be no reason you can’t do this. Also, you are on a fairly old version of Dundas BI and it might be worth an upgrade if you can as it might solve this for you.