comparing strings with date

[shruthibk]

hi all,

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

recid	date	status
1	1/1/2016	active
1	2/1/2016	retired
2	1/1/2016	active
2	2/1/2016	active
3	1/1/2016	active
3	2/1/2016	removed

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]

Maybe like

=Count(

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

[sunny_talwar]

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

[swuehl]

And maybe you need to format your variables using

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

[shruthibk]

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

[shruthibk]

it is not date format issue

[shruthibk]

i tried this expression this is also giving count 0

[shruthibk]

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]

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

Excluding values in Set Analysis