Using dynamic variable in the calculated fields

Question & Answer
,
1

I am using dateDiff function to find the number of days difference between two timestamp columns in my dataset. My requirement is to check this number of days is less than 14 and find the total number of records meeting this criteria. I have completed this task.

sum( ifelse( dateDiff( {d1}, {d2}, ‘DD’) <= 14, 1 , 0 ) )

However, I now have additional requirement to have flexibility to check against 7 or 14 days.

i.e., users wants to have the flexibility in the form of filter selecting 7 days or 14 days. Based on the selection, we should find the number of records.

Can someone please help how we can dynamically change the calculation?

Thanks in advance.

2

Hi @pbneelqs,
I would suggest setting up a parameter with your options, 7 and 14 days.
Then, for your calculated field, you can setup an ifelse statement that determines which calculation to run based on the parameter value selected. So you’ll setup your calculated field above to be utilized when the parameter value is set to ‘14’ or ‘14 Days’ (however you decided to word the parameter values).

ifelse(
${TimeInterval} = '14 Days', (sum( ifelse( dateDiff( {d1}, {d2}, ‘DD’) <= 14, 1 , 0 )),
$TimeInterval} = '7 Days', (sum( ifelse( dateDiff( {d1}, {d2}, ‘DD’) <= 7, 1 , 0 )), 
NULL)
````Preformatted text`

Something along those lines. Let me know if this type of solution could work for your case.
3

Hi @pbneelqs,
Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @pbneelqs,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!