How to create a calculated field with the condition that it should be within your date range filter?

Q&A
,
1

I’m new to quicksight so apologize if the question is basic but I couldn’t find an answer in the other posts.

I have a field called submit and date range filter called startTime. What I’m looking to create is a calculated field that counts the number of submit values with the dateCreated (another date field from another dataSource) that falls within the range the user selects for startTime.

I was thinking of doing a countIf and pass the startTime and dateCreated values in it but I can’t figure out how to use a function for “FallsWithinThisDateRange” for a single filter like startTime
Screenshot 2024-07-28 at 4.08.46 PM

2

Hi
For single date you can use select date filter.

3

Hi @Shahid_Muhammad, Can you elaborate?

I couldn’t find this as a function.

4

Hi @Rey,
If I’m reading your original question correct, you’re looking to create a calculated field with fields from 2 different datasets? Currently, you are unable to use fields from different datasets within the same calculated field. You could explore joining datasets.

Let us know if you have any additional questions!

1 Like
5

Hi @Rey,
Since we haven’t heard from you in a while, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!

6

Thank you for the response @Brett
My datasets are fine, what I’m looking for is:

I have a Date field like below. I want to create a calculated field for another of my fields (startTime and endTime) that works as below:

Count ({_id} , (If startTime and endTime fall within the range of dateCreated))

If I had two fields to compare the ranges I could do this, but I do not. I only have a dateCreated field and I cannot do something like:
Count ({_id} , ({startTime} >= dateCreated(startRange) AND endTime < dateCreated(endRange) ))

Please let me know if I could explain it better. Appreciate any insight

Screenshot 2024-09-08 at 10.25.45 AM

7

Hi @Rey,

You can do this by using parameters. Create 2 date parameters. Then, add a date picker control for each parameter.

Finally, write your calculated field like this:

countIf ({_id} , {Order Date} > ${StartDateParam}  AND {Order Date} < ${EndDateParam))

Replace Order Date in my example by your own date field.