Jump to content

How to filter a visualization to load with latest data based on the date range selection and the end date is less than the latest date available in the dataset.

siddharth sabat

Recommended Posts


I have often encountered various situations where I need to select the latest or max date based on aselected date range.Here is the situation..

Lets say I have a dataset with records ranging from 1st Jan 2021 to 30th Nov 2021. I want to restrict my visualization (either the table, pie chart or a bar chart) to load only data for the latest date based on the daterange I select (the end date here is less than the latest date available in the dataset i.e. = 30th Nov 2021). But If I select thedate filter with an end date

Link to comment
Share on other sites

  • 2 months later...

Hey @fabd, do you have any idea how to implement the same functionality on Bar and PIE charts. When I tried subsetting the vlaues in a PIE or bar chart using the above rule and expression, it filters the data with the values of latest date only. Even though I change the date to past, it only shows latest date data. Any way to make the subset work with the past days/periods filters. Appreciate your reply on this.



Link to comment
Share on other sites

Can you upload some sample data to your question


What do you exactly when you say "Even though I change the date to past, it only shows latest date data"


If you are not interested in the latest day you can try to work with ranking functions. Something like DenseRank([Date],"desc", [Cat]) = 1 would be the latest day, just like the Max() function. You can use more complex expressions to filter to other ranges. For example the two dates before the latest date:


DenseRank([Date],"desc", [Cat]) >= 2 AND DenseRank([Date],"desc", [Cat]) <= 3 

Link to comment
Share on other sites

Basically, I have a dataset like this. 

Date    Status    Category-1

1-Jan-21    On    A

1-Jan-21    Off    B

1-Jan-21    On    C

1-Jan-21    On    D

2-Jan-21    Off   A

2-Jan-21    On    B

2-Jan-21    Off    C

2-Jan-21    On    D

3-Jan-21    On    A

3-Jan-21    Off    B

3-Jan-21    Off    C

3-Jan-21    Off    DI want to create a PIE chart to show the total count of the On/Off status for each day. Shape by - Count(Status) So For 3rd Jan, Total count of On Status is-1 and Off Status is-3.

For 2nd Jan, Total count of On Status is-2 and Off Status is-2.My requirement here is, I want to have the date fileter with the configured filter type is a Range Filter. Now the problem with PIE chart when working with range filter is, it shows all the count of on/off status based on the whole date range selected.I want to display only the count of on/off status based on my end date selected in the date range filter. Meaning if the date range is selected from Jan 1 to Jan 3, the counts should be displayed for only 3rd Jan (not the total count from 1-3 Jan). If I select the date range from 1st to 2nd Jan, the counts should display for 2nd Jan (not from 1-2 Jan). Plesse advise if there is any expression we can achieve this. Thanks.

Link to comment
Share on other sites

Pie and Bar charts work differently compared to a table visualization. They always need aggregations. Therefore you need a more general approach.


You can make use of a data limitng expression and compare the [Date] column to the maximum date in the current filtering. You can store this specific max. date into a document property using a TERR data function. This function has to take your [Date] column with the active filtering as input. And it has to return the max. value of this:


max.date <- max(date.col)


Here, I called the input parameter "date.col" and the output parameter "max.date". The input is of type 'column' the output is of type 'value'. Note, even when your date.col has the data type 'date' it will be handled as 'datetime' object. So, the output will by a 'datetime' object. Also make sure to set checkbox to refresh your function automatically.


Now you can use this document property in any of your visualizations in a data limitng expression:



Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...