select one value from between date dashboard prompt

Question

Hello,

I have presentation variable that is build based on between dates dashboard prompt (so user chooses two dates from calendar), I can assign only one variable to this prompt. Then I am passing this presentation variable to column filter, this works fine however two values are being referenced whereas I only want the upper date to be passed to filter. Is this possible ?

Regards.

Jerry Casey · Answer

Glad I could help!

That being the case, would you mark my answer as "correct", so others with the same question will see it?

Jerry

User_YGBH8 · Answer

This is amazing, I am so thankful - works like a charm.

Jerry Casey · Answer

The two dates get passed as a string that looks like this: 2019-12-31,2020-01-29

This filter parses the second date using the position of the comma, then concatenates it into: date '2020-01-29'

Then casts it as date.

"Project Schedule Milestones"."Advertise Actual"<=CAST('date '''||SUBSTRING('@{dashboard.variables['varDates']}' FROM POSITION(',' IN '@{dashboard.variables['varDates']}')+1 FOR 20)||'''' AS DATE)