6 Replies Latest reply on Nov 26, 2018 7:12 AM by ShivaRam Chennapragada

# Calculated field help - IF and Count

Hi

I need two calculated field which essentially says:

1) If date opened is in 2018, and case status is "open/active" and date suspended is populated with a date then number of records.

2) if date opened is in 2018, and case status is "open/active" and date suspended is null (blank) then number of records.

I would also like, if possible, to compare these to 2017, so I would end up with something like this:

2017      2018

Suspended - open/active          2          1

ongoing - open/active                0          1

Thanks very much

• ###### 1. Re: Calculated field help - IF and Count

Try these,

1) SUM(IF [Case Status] = 'Open/Active' AND YEAR([Date opened]) = 2018 AND NOT ISNULL([Date Suspended]) THEN [Number of Records] END)

2) SUM(IF [Case Status] = 'Open/Active' AND YEAR([Date opened]) = 2018 AND ISNULL([Date Suspended]) THEN [Number of Records] END)

• ###### 2. Re: Calculated field help - IF and Count

This is just what I need, thanks Shivaram, much appreciated

• ###### 3. Re: Calculated field help - IF and Count

Hi Anthony;

Is this what you're looking for? The Status equation is simply:

Status

if isnull([Date Suspended]) then "Suspended" else "Ongoing" end

Hope this works! --Michael

• ###### 4. Re: Calculated field help - IF and Count

I guess I should refresh my browser before posting my answer!

• ###### 5. Re: Calculated field help - IF and Count

Perfect, thanks Michael, I couldn't work out how to put in a table form so this is great. Much appreciated.

• ###### 6. Re: Calculated field help - IF and Count

Haha Michael, happens to me all the time.