Get the latest value based on the latest date

likhitha · April 19, 2023, 3:00pm

Hi there,
I want to get a column value based on the latest date.
For eg: I want to get latest value in ‘price’ column order by ‘date’ field desc. I want to refresh this data daily so that every day the visual displays the latest price based on the available latest date.
I tried with ‘last value’ and did not get the expected result. Any help is appreciated.

Sanjeeb2022 · April 19, 2023, 3:40pm

Hi @likhitha - I believe this possible, can you please give some sample data and expected output so that it will be easy to replicate and guide you solution.

Tagging @Biswajit_1993 as well for his feedback.

Regards - San

likhitha · April 19, 2023, 4:16pm

Hi @Sanjeeb2022 As you see in the attached screenshot, I want the output to point out the latest date that is available when the dataset is refreshed.
Thanks.

Sanjeeb2022 · April 21, 2023, 5:59pm

Thank you @likhitha for the details. I think you can achieve this by below steps.

Step -1: Select the graph type "Key Performance Indicator - KPI)
Step -2: In the value put the sales column and choose Max.

See the screenshot below.

Hope this will help you in solve your problem. If it is resolving your issue, please marked as solution so that it can help other community members.

Regards - Sanjeeb

Wakana · April 27, 2023, 2:06am

hi @likhitha,
Did Sa jeeb’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

likhitha · May 3, 2023, 4:13pm

No, this is not resolved.
@Sanjeeb2022 I want to display the sales value based on the latest date.
The solution you provided just gives the max value of the sales.

Sanjeeb2022 · May 3, 2023, 7:05pm

Hi @likhitha - Ok, the requirement is to filter the data on the basis of max date? Please correct me If I am wrong.

Tagging @Biswajit_1993 for quick feed back on this.

Regards - Sanjeeb

likhitha · May 3, 2023, 7:14pm

That’s right. For the max date, I want to display the ‘sales’ column.
My output should only display ‘sales’ and the value for that sales.

Thanks

Sanjeeb2022 · May 3, 2023, 7:15pm

Ok @likhitha.

Let’s wait @Biswajit_1993 feedback as i Know he implemented same solution. or else I will do a poc from my side and update you.

Have a great week ahead.

Regards - Sanjeeb

Biswajit_1993 · May 4, 2023, 1:56pm

Hi @likhitha ,

Thanks for raising your query, yes you can achieve this by using parameter in QuickSight.

Please follow some simple steps to get the desired result.

Step -1 Create one default DateTime Parameter as LatestDate.

Go to Add menu and select Add Parameter then select DataType as DateTime, Time Granularity as Day & Default Date as Relative Date.

Then put the filter condition as Start of This Day

Step -2 Activate the parameter.

Once you created the parameter then you need to activate the parameter on the Date filter for your analysis.

For this you need to add the Date filed into the Filter menu and select the filter type as Relative Date , Time Granularity as Days then select Date & Time from a parameter and pass the parameter in this section.

PFB the picture for your understanding.

Step - 3 Testing

Then use parameter as control and do testing how the result is showing.

Please check whether this solution is work for you or not if not please revert back.

Thanks & Regards
Biswajit Dash

Sanjeeb2022 · May 4, 2023, 4:43pm

Thanks @Biswajit. This is awesome.

Regards - Sanjeeb

Yuval.Shiboli · January 2, 2024, 1:00pm

Hi @Biswajit_1993 - I have a requirement similar to Sanjeeb2022 ‘s but I want the latest data to be presented as a cell in a table/pivot table.
I’m looking for a way to show accounts’ quarterly usage, and I want to indicate the name of the current account owner. since account owners are switched now and then, I am trying to create a calculated field that will always indicate the current account owner.
I need some guidance to find the right formula here…

thanks, Yuval

leandrocs · April 11, 2024, 7:09pm

The proposed solution didn’t work for me, but I found a way to my case and I’d like to share:

I’m assuming you’re using a KPI visual, so you’ll want to add a filter for the {Date}, filter type “Top and bottom filter”, ‘Top’, Show top 1 Days, BY {Sales}, Aggregation ‘Count’. Then add a tie breaker based on {Date}, Aggregation Max, sort Descending.

What that’ll do is to first get the top 1 based on the count(Sales), so whenever the Sales is a non-null value, the count will be 1, if it’s null, it’ll be 0, so we just get the non-null values. Then, for the tie breaker, we actually get the top 1 based on the most recent date. And that will be your latest Sales value based on date.

Hope that works.