Calculating Custom Fields - Dashboards

Hi all,

I'm fairly new to SQL text, but I can usually get by. However, I'm stuck here.

The problem is simple: I want to calculate our overall "renewal" rate; that is, the percentage at which our clients renew with us by hiring us again.

The first two problems to solve are distinguishing between our "Client" and "Internal" projects, which I do by the following formula

SUM(CASE WHEN "Client or Internal" = 'Client' THEN 1 ELSE 0 END)

This works as it should.

The next problem is adding together every instance where a client has renewed. I created a custom field linked with Projects called "Renewal" (a simple Yes or No answer), so my formula is :

SUM(CASE WHEN 'Renewals' = 'YES' THEN 1 ELSE 0 END)

This formula does not work. 

Can anyone help me out ?

Thanks

0

Comments

1 comment
  • Steve

    Without knowing much about the type of graph and metric you are using I did a quick test with a YesNo custom dropdown and use this on a bar chart. My calculated field using your formula works fine. My test is very simple and the count of 2 corresponds with the number of records I have with Yes selected in my dropdown. I assume that you are using the same capitalization in your query than in your dropdown?

     

    0
    Comment actions Permalink

Please sign in to leave a comment.