Forum Discussion

nileshmahadik's avatar
nileshmahadik
Data Storage
11-01-2023
Solved

Create dashboard from max two dates

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-ca...
  • Liliia_DevX's avatar
    Liliia_DevX
    11-08-2023

    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

nileshmahadik's avatar
nileshmahadik
Data Storage
11-07-2023

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's avatar
Liliia_DevX
Sisense Employee
11-08-2023

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