Create dashboard from max two dates

[nileshmahadik]

Hi,

We have an requirement to create dashboard to select max date from combination of two dates, tried with below suggestion but its not worked.

https://community.sisense.com/t5/knowledge/how-to-calculate-the-maximum-minimum-date-per-category-on...

SQL query is look like below to populate report. Can some one help on this requirement ? Creating sql query to populate table and add into elastic cube is one of the option but wanted check any other way to handle this?

SELECT

* FROM

  (select t.id, MAX(t.final_date) as  max_date from 

( SELECT id,

  CASE

      WHEN date_1 IS NOT NULL  THEN date_1

      ELSE date_2

  END AS final_date

  FROM table ) t

GROUP BY t.id   ) AS latest_dt

INNER JOIN

  table ON  table.id = latest_dt.id and 

  ( latest_dt.date_1 = table.date_1 or latest_dt.date_2 = table.date_2)

[Liliia_DevX]

Hi,

If you have different dates stored within one column and need to choose the most recent one, it's possible to achieve it following the approach described in the "Calculating Values For The Most Recent Date" guide. 

The main idea is to convert a date into integer in the ElastiCube using a formula below: 

getyear([Date])*10000+getmonth([Date])*100+getday([Date])

After you build your ElastiCube, you need to add this newly created column into the "Values" section in a pivot and choose its max value:

Then add the TOP 1 filter by the max date integer value and hide the actual value in the pivot:

If you have multiple date columns stored in different tables, then the easiest approach will be a custom table or a custom import query with SQL like you developed to join the tables and choose the max date. After that you can use just one column with max date in a widget without additional filtering. 

Regards, Lily

[nileshmahadik]

Hi Lily,

Thank you for your reply.

Dates are stored into two different columns so we need steps like

• Compare date1 and date2.
• Find out maximum between both dates for each row
• Get maximum of date from above step.

Is it  possible to create dashboard with above requirement?

[Liliia_DevX · Accepted solution]

Hi,

Thanks for the additional details. Since there is no functionality within a chart or pivot to manipulate the data within rows (the categories), it's not possible for now to compare dates on a widget level to choose the max date from two different columns.

It's possible to compare dates within a calculation by using functions like DDIFF, for example. (See "Dashboard Functions Reference")

If you need to compare two different dates within one table, then you can create a custom column with the code like below:

CASE WHEN [Date1] > [Date2]
THEN [Date1]
ELSE [Date2]
END

After that you'll be able to use this custom column as a row in a chart or pivot to display the max date. 

If your date columns are located in different tables, please take a look at the lookup() function to import a field from a remote table into the current table first and then try the same approach. 

Hope it helps.

Best Regards, Lily