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]

Hi,

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

[Fabian Duerr]

You can use the subset option in the properties of your visualization.

This worked fine on my data:

[siddharth sabat]

Awesome! Thanks Fabd, it worked for me as well. Thanks for your respone. Appreciate it.

 

 

 

 

[siddharth sabat]

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.

 

 

[Fabian Duerr]

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 

[siddharth sabat]

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.

[Fabian Duerr]

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:

 

[Date]=Date(DocumentProperty("max.date"))

[siddharth sabat]

Thanks @Fabd. The expression through the document property pointing to the data function worked to limit PIE/Bar chart.  Many thanks for your reply.