Former Member

### Getting formula calculation to show on graph with a monthly grouping

Greetings,

I need help in a summary formula that will require groupings. The graph below works well in showing how many are eligible for a survey (green) vs. how many were actually mailed a survey (orange).

a1.jpg

The customer would like me to add a bar to this graph to show the number of people who were eligible for a survey but were not mailed one (thus, I’d be adding a bar to show the difference between the green and the orange bars already present).

Green bar: COUNT ({Eligible})

Orange bar: This is simply a sum of the column containing either a 0 (no survery mailed) or 1 (survey mailed) : SUM ({MAILED})

The report has parameters for Country, Date, City (in that order) and is set for all countries.

OK, so the basic core logic is very simple since I just need a formula to show : COUNT ({Eligible}) - SUM ({MAILED}). The trick is to getting this formula to group on the graph in a way that corresponds to each month.

As a 1st step in this process, I made a formula in an attempt to show just the Count of customers that are Eligible for that month. Since I already have a green bar showing the count of customers each month, I was hoping in my experiment here that my formula would also show the exact same count of eligible customers for each month. Here is the formula

COUNT({Command.ELIGIBLE},{Command.EVENTDATE}, "monthly")

I get results on my graph as shown below but the count of customers only includes the count from the 1st city for each month (instead of the entire country for that month). I am not sure what to do since the formula does not appear to accept multiple groupings.

a2.jpg

Anyway… I hope I am making sense and that you might have some suggestions for me. What I want to do seems so simple (calculate the difference between the green and orange bars) so that I can add a bar to the graph showing the customers who were eligible for a survey but did not receive one (seems simple but struggling to achieve) Many Thanks!

a1.jpg (161.7 kB)
a2.jpg (181.7 kB)