How to obtain the minimum date

#1

Hello,

I'm trying to get the oldest date from my data from the dashboard to calculate the difference between the current day and that oldest date. But I can't seem to get that minimum date , because my date it's a hirarchie and also should be a slicer for my metric. How can I do that? Does it need to be done in a cube?


Thank you

#2

Hi Carolina,


Yes it's possible to do it in the data cube. You can use manual query select with MIN function to get the oldest date. like this:

SELECT MIN(DateColumn) AS OldestDate FROM TableName



Next, you would create a calculated element that calculate the difference between the current day and that oldest date. like this:

DateTime date1=$OldestDate$;

TimeSpan span=DateTime.Now-date1;

return span;



You should also be able to do it with some dashboard scripts. If you sort the date columns first so the oldest date comes first, then you can grab the date value from the metricset's dateresult and do some calculation. However, it would be more straight forward with manual query and calculated element.


You can find more details about calculated element here:

https://www.dundas.com/support/learning/documentation/data-metrics/transforms/common/calculated-element


#3

Thank you very much for you answer.

But I was looking for a way to do it from the dashboard, because if I do it from a data cube with a manual select I would have to add a where condition with some placeholders (I guess ), so I can pass them to the filters in the dashboard. (because I need to get the oldest date in a range of dates and some other specific status) That's why I thought I should be easier form the dashboard using the slicers (I just don't know a way to calculate things over hirarchies) , but I'll give it a try from the cube to see if it works fine.


Thanks again..

#4

You are welcome Carolina.


By the way, if you want to retreive the date via script, you can put the data inside a data, the script example for retreiving data can be found here:

https://www.dundas.com/support/developer/script-library/data/get-click-or-interactive-data