Need to create a single date field for the X-axis in the bar chart

Q&A
,
1

Hello Everyone,

I am new to Quick Sight and need some help on one of the requirements I’m working on.

There are two tables A and B which have been joined by a full outer join in the database based on A.Date_Field_1 & B.Date_Field_2. Because of the
full outer join, NULL values are coming in both the date fields i.e. Date_Field_1 & Date_Field_2 for unmatched records. Please find the range of the
two date fields below-

A.Date_Field_1= Aug 7,2023 - Dec 26,2024
B.Date_Field_2= Jul 15,2020 - Jun 24, 2025

The Quick Sight dataset has been created on top of the custom query which joins tables A & B. Now, i need to create a bar chart and the date field obviously should come in the X-axis. Because of the NULL values, I’m unable to use either of the two date fields as it will pull the NULL values also.

Now to use a single date field for the X-axis in the bar chart while dealing with a full outer join of the two tables with overlapping date ranges, i want
to create a calculated field that determines the overall date range and use that for the X-axis. Specifically, i want to use the min() and max() functions to find the earliest and latest dates across both date fields and create a single date range.

Now the problem is that when I’m using the min(A.Date_Field_1,B.Date_Field_2), it throws the error-“At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again”. I tried my best but couldn’t fix this.

So, i created the min and max values by using the following logic and got the min and max value as Jul 15,2020 and Jun 24, 2025.

Min Date= min(ifelse(isNull(A.Date_Field_1),B.Date_Field_2,A.Date_Field_1))
Max Date= max(ifelse(isNull(A.Date_Field_1),B.Date_Field_2,A.Date_Field_1))

Now, per one of the knowledge articles, i tried the below logic to have a single date range-

ifelse(min (Date_Field_1,Date_Field_2) < max (Date_Field_1, Date_Field_2), min (Date_Field_1, Date_Field_2), Date_Field_1) but like i mentioned above, the
min(A.Date_Field_1,B.Date_Field_2) wasn’t working so to make this logic work, i replaced it with the min and max functions that worked for me-

ifelse(Min Date < Max Date, Min Date, A.Date_Field_1) but then i was getting the error- “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

So, i just need to create a single date field out of these two date fields so that i can use it in the X-axis of the visual. Can someone help with this?

2

Hi @Dbh ,

Welcome to the Quick Sight Community!

Min() and Max() in Quick Sight act as Aggregation function only and does not work as logical function min(value 1, value2).

If you are looking to fetch Min between 2 numbers, ifelse(a<b,a,b) is the best bet.

However the requirement you stated, requires a different approach since you are trying to collate both the field values. I can think of creating a calculated date field by using coalesce(field1, field2). And use this field in the x axis.

Try this out and let us know if this works out.

Thanks,
Prantika

3

Hi @Dbh

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hello @prantika_sinha - Thanks for your response. I achieved it using the following syntax which pulled data from Jul 15,2020 - Jun 24, 2025 but your approach is also correct.

ifelse(
isNull(A.Date_Field_1),B.Date_Field_2,
isNull(B.Date_Field_2),A.Date_Field_1,
A.Date_Field_1
)