topic comparing strings with date in QlikView

comparing strings with date

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

hi all,

i am trying to compare the string with previous and current month date

i am trying to find the count of recid if the previous month status is active and the current month status is retired or removed

i have tried using if condition

sum(if((date=vPM and status='active') and (date=vCM and match(status,'retired','removed'))

,1,0))

where vPM=date(addmonths(max(date),-1),'MM/DD/YYYY') and vCM=date(max(date),'MM/DD/YYYY')

but i am getting sum as 0 please help me to resolve this

regards,

shruthi

swuehl — Wed, 13 Apr 2016 15:51:49 GMT

Maybe like

=Count(

{<recid = p({<date = {'$(vPM)'}, status = {'active'}>}) * p({<date = {'$(vCM)'}, status = {'retired','removed'}>}) >} DISTINCT recid)

sunny_talwar — Wed, 13 Apr 2016 15:52:07 GMT

Are you doing this in the script or the front end of the application?

swuehl — Wed, 13 Apr 2016 15:55:58 GMT

And maybe you need to format your variables using

vPM=date(addmonths(max(date),-1),'M/D/YYYY')

shruthibk — Thu, 14 Apr 2016 06:51:35 GMT

hi i tried in script as well as front end side both side not working

shruthibk — Thu, 14 Apr 2016 06:52:04 GMT

it is not date format issue

shruthibk — Thu, 14 Apr 2016 06:54:24 GMT

i tried this expression this is also giving count 0

shruthibk — Thu, 14 Apr 2016 07:58:59 GMT

Hi this is giving the desired count one more requirement how can i count the null values in expression for example

along with retired and removed if the value is null it should count that value as well

swuehl — Thu, 14 Apr 2016 09:17:01 GMT

NULL is not a value and can't be selected. But you can derive the records to include indirectly, e.g. using e() function. Have a look at