## COUNT and SUM for true/false tested values - can't summarise across a group?

## COUNT and SUM for true/false tested values - can't summarise across a group?

(OP)

I am preparing a report to test add-on sales.

The report pulls all qualifying product lines of the base product, plus all associated add-on sales where there were any. The report is grouped by pa_order_no and then has a summary for each sales rep. I have a DISTINCTCOUNT for pa_order_no for each sales rep, then I have formula @protector which delivers a TRUE/FALSE for each of the specified add-on lines.

What i need to be able to do is summarise the add-on TRUE/FALSE so that I end up with a value I can use to calculate conversion rate. Here is a sample of data which shows my issue - I need the @protector total to be 2, so the overall conversion rate is 100%, not 200%:

Order Item DistinctCount @Protector Conv Rate %

300566763 1 0

300566763 2 1

1 1 100%

303066752 1 1

303066752 2 1

303066752 3 1

1 3 300%

Total 2 4 200%

I have tried all manner of variable methods, but I always come up against the same issue - namely that I can't get the @protector value to equal either 1 or 0 for each distinct order, depending on whether there was an @protector add-on sold or not. The conversion rate can only ever be 100%!

Any ideas gratefully received.

Many thanks

The report pulls all qualifying product lines of the base product, plus all associated add-on sales where there were any. The report is grouped by pa_order_no and then has a summary for each sales rep. I have a DISTINCTCOUNT for pa_order_no for each sales rep, then I have formula @protector which delivers a TRUE/FALSE for each of the specified add-on lines.

What i need to be able to do is summarise the add-on TRUE/FALSE so that I end up with a value I can use to calculate conversion rate. Here is a sample of data which shows my issue - I need the @protector total to be 2, so the overall conversion rate is 100%, not 200%:

Order Item DistinctCount @Protector Conv Rate %

300566763 1 0

300566763 2 1

1 1 100%

303066752 1 1

303066752 2 1

303066752 3 1

1 3 300%

Total 2 4 200%

I have tried all manner of variable methods, but I always come up against the same issue - namely that I can't get the @protector value to equal either 1 or 0 for each distinct order, depending on whether there was an @protector add-on sold or not. The conversion rate can only ever be 100%!

Any ideas gratefully received.

Many thanks

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

From what I am understanding you just want a unique count of the Order Items (not sure what criteria when you actually want to count the Order Item).

If so, I would use a running total. Do a distinct count of the Order Items (you may put in what criteria into a formula for the Evaluate) and it would never reset. Running totals give a lot of flexibility for running total. Otherwise you may need to create a manual running total (with formulas).

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

The data didn't look like that when I entered it! See below hopefully a better layout.

This shows the details set-out in the initial post. The essence of my issue is that for orders such as 303066752, I need to be able to show a COUNT or SUM for the order that equals 1 for @protectors (as opposed to 3 which it will at present), as although there were 3 separate qualifying products sold, the conversion rate can only be, for any order, either 100% or 0%.

The @protector formula is:

if {peranal.pa_prod_cd} like ["TEMP30MPLONG", "TEMP26MPSTAN", "TEMP30MPSTAN", "TEMP46MPSTAN", "TEMP46MPLONG", "TEMP50MPLONG", "TEMP60MPLONG",

"SUPE26PRLONG", "SUPE30PRSTAN", "SUPE30PRLONG", "SUPE40PRSTAN", "SUPE46PRSTAN", "SUPE50PRLONG", "SUPE60PRLONG",

"COTT26PRLONG", "COTT30PRSTAN", "COTT30PRLONG", "COTT40PRSTAN", "COTT46PRSTAN", "COTT50PRLONG", "COTT60PRLONG",

"SIMB30PRSTAN", "SIMB46PRSTAN", "SIMB50PRLONG", "SIMB60PRLONG",

"LUXU30PRSTAN", "LUXU40PRSTAN", "LUXU46PRSTAN", "LUXU50PRLONG", "LUXU60PRLONG",

"TEND26PRLONG",

"PREM26PRLONG"] then 1.00 else 0.00

Hopefully the above better explains the problem?

Many thanks in anticipation.

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

maximum({@protector},{Table.order})

Then format it the formula to show percentage.

-LB

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

However, while that works at {order} level, I still need to be able to SUM or COUNT the 'maximum({@protector},{Table.order})' across all the salesperson's orders, so I can calculate a conversion rate per salesperson, as well as per order.

Any ideas?

Thanks

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

Is there only one sale person per order?

What is your group structure? Group #1 = salesperson and Groupo #2 = Order?

-LB

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

Yes, there is only one salesperson per order.

The report groups are:

Group 1 - Branch

Group 2 - Salesperson

Group 3 - Order

Hope that helps.

Many thanks

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

//{@ordermax} which also serves as the display formula for the orders level:

Whileprintingrecords;

Numbervar br; //for the branch level

Numbervar slp; //for the salesperson level

Numbervar ord := maximum{{@protector},{table.order});//for the order level

slp := slp + ord;

br := br + ord;

ord

Then create two reset formulas:

//{@branchreset} to be placed in the branch group header:

Whileprintingrecords;

Numbervar br := 0;

//{@salespersonreset} to be placed in the salesperson group header:

Whileprintingrecords;

Numbervar slp := 0;

Then create two display formulas:

//{@branchdisplay} to be placed in the branch group footer:

Whileprintingrecords;

Numbervar br:

//{@salespersondisplay} to be placed in the salesperson group footer:

Whileprintingrecords;

Numbervar slp;

-LB

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

Brilliant!!!! They all work as expected.

One final question though - how do I do a report total (i.e. all branches)?

Many thanks

## RE: COUNT and SUM for true/false tested values - can't summarise across a group?

Then right before the last line in the formula (ord) add this:

all := all + ord;

You donâ€™t need a reset, but need to add a display formula in the report footer:

Whileprintingrecords;

Numbervar all;

-LB