Counting Dates on a date rage

jferrara
jferrara Member
in Charting

In my business we sell a monthly service.  A customer is either acivated or deactivated.  I have a dataset that has 2 date columns (activation date and deactivation date).  I can easily create 2 separate cards that show the number of activations and deactivations every month.  The activation card would have the activation date (X axis) and count of activation date (Y axis).  However, I want to combine those 2 cards so that I can see the number of activations every month compared to the number of deactivations every month.  How do I do this with the Date Rage Field \ X axis being restricted to one of those 2 dates?

Best Answer

  • ccpincombe
    ccpincombe Member
    Answer ✓

    Hi Jferrara, 

     

    You'll need to do some sql on the backend (at least that's what we've done) that essentially adds a new line for every month that a customer is active with a count of 1. Then, when the customer is listed as deactivated, it returns a value of -1. This way, you'll have a new field of Customer Status, and your customer will be listed as "Active" every month until they are deactivated, where their status will then change to "Inactive". We used the mysql tool to write this code. Thanks!

Answers

  • ccpincombe
    ccpincombe Member
    Answer ✓

    Hi Jferrara, 

     

    You'll need to do some sql on the backend (at least that's what we've done) that essentially adds a new line for every month that a customer is active with a count of 1. Then, when the customer is listed as deactivated, it returns a value of -1. This way, you'll have a new field of Customer Status, and your customer will be listed as "Active" every month until they are deactivated, where their status will then change to "Inactive". We used the mysql tool to write this code. Thanks!

  • kshah008
    kshah008 Contributor

    @jferrara, did ccpincombe's reply help you out?

This discussion has been closed.

Categories