2 Replies Latest reply on Nov 14, 2016 1:04 PM by Jordan Ross

    Limit and Label Title of Displayed Date to Most Recent Data

    Jordan Ross

      Hi,

       

      I am creating a dashboard that will be used to create weekly pdf reports. There are several views that will be limited to data from the past week, 2 weeks, and 2 months (60 days).

       

      I have been able to limit the displayed data to the selected time frame fairly easily by creating a dimension called "Max Reported Datetime" using the following calculated field:

       

      {fixed: MAX([Event Reported Datetime])}

       

      Another calculated field is made called "Past 60 days Filter" using the following code :

       

      if ([Event Reported Datetime]) >= ([MAX Reported Datetime]-59) then 1 else 0

      END

       

      This is then put into the filter shelf with values limited to "1", and only data for the past 60 days beginning from the most recent day that data was received is displayed.

       

      I then create titles for the various charts using two more calculated fields called

      1. Max Datetime, which is made using:    (max([event reported datetime]) )
      2. Max Datetime - 59, which is made using:     [Max Datetime] - 59

       

      This system has worked for the majority of views in the workbook. However, there is one page that lists every facility separately, with counts/rates for each day. Not all facilities provide data as reliably/regularly, so for some facilities the most recent data could be several days behind another facility. When I use the above two fields to label this view, it does something funny that doesn't happen in the other views where they facilities are aggregated: It displays each calculated field as a range instead of a specific date. For example, it will display Max Datetime as "September 9th, 2016 to October 4th, 2016" and then Max Datetime-59 as each of these dates minus 59 days. (Note: the 60 day filter still works properly, with all data only displaying the 60 days from most recent data received).

       

      I tried changing Max Datetime code to:     WINDOW_MAX(MAX([Event Reported Datetime]))           but is still results in the same issue, displaying a range of dates.

       

       

      Is there a way to limit the title to give the data that most recent data was received for all facilities combined?

       

       

       

      All of the above can be replaced using the system date instead of last date that data was received, but due to delays in reporting that can occur these dates do not always match up with the dates of displayed data.