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 1^{st} 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 1^{st} 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!

## Add comment