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-calculate-the-maximum-minimum-date-per-category-on-pivot2/ta-p/9115

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'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

3 Replies

Replies have been turned off for this discussion
  • Liliia_DevX's avatar
    Liliia_DevX
    Sisense Employee
    11-07-2023

    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'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