select one value from between date dashboard prompt

User_YGBH8

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.

User_YGBH8

I have ended up in such BI sql clause "DIM - CDATE.DATE" =  last(date '2019-12-01',date '2020-01-29')), how from such two values bracket list select only second one using BI SQL ?

Jerry Casey

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)

User_YGBH8

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

Jerry Casey

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