Set analysis with variable

[Anonymous]

Need help to write set expression using variable.

My current expression is:

count(DISTINCT(If(SLA_INDICATOR='SLA_MET' and Ticket_Type ='BPS Tickets' and [Referred to CPS] ='Y' and [Resolved Year]=Year(today()) and [Resolved Date]<=vInputDate+1 ,[TR #])))

I tried using the below to write the set expression with variable but its not working

count(DISTINCT {<SLA_INDICATOR={'SLA_MET'},Ticket_Type={'BPS Tickets'},[Referred to CPS]={'Y'},[Resolved Year]={"$(=year(today()))"},[Resolved Date]={"<=$(vInputDate+1)"}>}Id)

Can anybody please help how to write the expression with the variable?

[sunny_talwar]

Try this:

Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved Date] = {"<=$(=vInputDate+1)"}>}Id)

[pipuindia99]

Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved Date] = {"<=$(=vInputDate+1)"}>}Id)

What Sunny has given should work...

[Anonymous]

Hi Sunny

This doesnt seem to work. I have defined through input box and not in the load script

[sunny_talwar]

What is the resolved field format? May be you need that here:

Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved Date] = {"<=$(=Date(vInputDate+1, 'DateFieldFormatHere'))"}>}Id)

[Anonymous]

My [Resolved Date] is in timestamp format such 11/16/2016 12:57:17 PM and vInput date is in M/D/YYYY format so I changed the expression to the below but its still not working..

=Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved date] = {"<=$(=timestamp(vInputDate+1),'M/D/YYYY h:mm:ss TT')"}>}Id)

[sunny_talwar · Accepted solution]

Try this (you might have misplaced a parenthesis):

=Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved date] = {"<=$(=timestamp(vInputDate+1),'M/D/YYYY h:mm:ss TT')"}>}Id)

Try this:

=Count(DISTINCT {<SLA_INDICATOR = {'SLA_MET'}, Ticket_Type={'BPS Tickets'}, [Referred to CPS] = {'Y'}, [Resolved Year] = {"$(=Year(Today()))"}, [Resolved date] = {"<=$(=timestamp(vInputDate+1,'M/D/YYYY h:mm:ss TT'))"}>}Id)

[Anonymous]

Thanks Sunny. It worked. I think the bracket was missed out