petejigsaw
MIS
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