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

[petejigsaw]

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

 

[kray4660]

A couple of things to note. One your data example is hard to read and understand what data is what. Second. You did not provide the text of @Protector.

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).

 

[petejigsaw]

Sorry Kray4660

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.

 

[lbass]

Try using a maximum:

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

Then format it the formula to show percentage.

-LB

 

[petejigsaw]

Thanks lbass

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

 

[lbass]

Two questions:

Is there only one sale person per order?

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

-LB

 

[petejigsaw]

Hi lbass

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

 

[lbass]

Create this formula and place it in the order group footer:

//{@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

 

[petejigsaw]

Hi lbass

Brilliant!!!! They all work as expected.

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

Many thanks

 

[lbass]

Add another variable to the {@ordermax} formula in the list of number variables and call it numbervar all;

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