How to display min(date) - max(date) in text object visual

Question & Answer
,
1

Hi Experts,
I am struggling with a simple ask.
Just wanted to capture min(date) and max(date) and display in text object.
Something like below.

Biweekly Operation Report
Jan 1,2024 - Jan 15 ,2024

Can we achieve this in quicksight ?
Highly appreciate your help.

2

Hi @syed_imran
yes and no. first of all you need to create a calculated field (min - max). Then you can use a table visual to display it.
BR

3

I used this formula
concat(toString(min(twoweekdate)),‘-’,toString(max(twoweekdate)))
but i get 2024-02-05 00:00:00.000-2024-02-18 00:00:00.000.
Can we format this to Feb 5,2024 - Feb 18 , 2024?

Highly appreciate your help.

4

Dont know if there is a simple way to capture the min(date),max(date) in a parameter

5
6

How do i format a range using formatdate?

7

even if i wanted to pass a min(date) in an expression there is no away i can do it.
Simple expression : trying to pass date in an expression where date is between min and max of other date column.

8

Hello @syed_imran,

Initially, you need to format the Date before going to use this formula.

Best,
vaibhav

9

hello @syed_imran,

Else you can create 2 date parameters.
1st should be for the max date and 2nd would be Min date and you can set the relatives dates for each other.

Best,
Vaibhav

10

i cannot concat dates using concat function.They have to be changed to toString for concat function to work.

1 Like
11

So you can do like this,
Please take a reference from SS.

image
image126×152 3.42 KB

or simply go with parameter method

12

Hi @Vaibhav.narwade

something like_

concat(toString(formatDate(mindate, "MMM d,yyyy"))," -",toString(formatDate(maxdate, "MMM d,yyyy")))

BR

13

image
image798×524 9.39 KB

14

looks like formatDate only excepts format like in

concat(toString(formatDate(mindate, “MMM-dd-yyyy”))," -",toString(formatDate(maxdate, “MMM-dd-yyyy”)))

seems you have to extract each part and concat it in the way you want.

concat(substring(toString(formatDate(mindate, “MMM-dd-yyyy”), 1, 3)," ",substring(toString(formatDate(mindate, “MMM-dd-yyyy”), 5, 2),",",substring(toString(formatDate(mindate, “MMM-dd-yyyy”), 8, 4)," - ",
substring(toString(formatDate(maxdate, “MMM-dd-yyyy”), 1, 3)," ",substring(toString(formatDate(maxdate, “MMM-dd-yyyy”), 5, 2),",",substring(toString(formatDate(maxdate, “MMM-dd-yyyy”), 8, 4))
2 Likes
15

Thanks Erik.
Will go with the @Vaibhav.narwade’s Solution.
Did not expect Just for a header will have to go with such a long expression.

Anyways thanks a lot.
Appreciate your help.