cancel
Showing results for 
Search instead for 
Did you mean: 

Create dashboard from max two dates

nileshmahadik
7 - Data Storage
7 - Data Storage

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)

 

 

1 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

View solution in original post

3 REPLIES 3

Liliia_DevX
Sisense Team Member
Sisense Team Member

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])

Liliia_DevX_0-1699350592141.png

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:

Liliia_DevX_1-1699350797260.png

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

Liliia_DevX_2-1699350920347.png

Liliia_DevX_3-1699350933407.png

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

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?

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