topic Re: Set analysis with variable in QlikView

Set analysis with variable

Anonymous — Tue, 07 Feb 2017 11:24:05 GMT

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?

Re: Set analysis with variable

sunny_talwar — Tue, 07 Feb 2017 12:23:08 GMT

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)

Re: Set analysis with variable

pipuindia99 — Tue, 07 Feb 2017 13:15:26 GMT

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...

Re: Set analysis with variable

Anonymous — Thu, 09 Feb 2017 07:18:10 GMT

Hi Sunny

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

Re: Set analysis with variable

sunny_talwar — Thu, 09 Feb 2017 10:10:12 GMT

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)

Re: Set analysis with variable

Anonymous — Thu, 09 Feb 2017 11:56:42 GMT

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)

Re: Set analysis with variable

sunny_talwar — Thu, 09 Feb 2017 12:08:38 GMT

Try this (you might have misplaced a parenthesis):

Try this:

Re: Set analysis with variable

Anonymous — Fri, 10 Feb 2017 08:40:06 GMT

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