1

I having trouble finding a way to ratio Summary data against Group Level data (not the detail Level)

A simplified version of the problem is as follows:

Say, I have two tables with a one to many relationship:

stock LEFT OUTER JOIN sales ON stock.barcode=sales.barcode

The report is layout is as follows: 

GroupHead2 |
GroupHead1 | Sales$                       %             Stock$      %
Details    | Sales
GroupFoot1 | a=Sum(Sales,{Group1})       a/b            c=Stock      c/d
GroupFoot2 | b=Sum(Sales)                               d=Sum(Stock)

I can get the ratio a/b to work as follows:

a/b = Sum({Sales},{Group1})/Sum({Sales})

But "d" is a running total (made with the running total wizard) because I only want the stock on Hand for each barcode Vs the sum of sales for each barcode.

Thus if I try to get the ratio "c/d" I receive an error.

EDIT: Output looks as follows - Say:

Sales$   %            Stock$   %     
200      13.3         50       100
300      20           20       28.5 
800      53.3         30       30
200      13.3         10       9.1
----------------------------------------------------------------
1500                  110

Note the Percentage stock is wacky because it is recalculating the total (running) on each line instead of using the total value (eg 110)

EDIT 2:

I saw this as a Similar Question and was hoping someone could offer the adaption (which has escaped me) of this answer to suit the above scenario.

The answer is as follows:

Sum ({ numeric field }, { grouping item }) % Sum ({ numeric field })

The problem is slightly different to mine as the { numeric field } is introduced at the group 1 level thus it only needs to be evaluated once per group 1.

I'm guessing there maybe a way to get "d" without using a running total (?) or a better way to make the ratio "c/d"?

Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
Bryn
  • 103
  • 1
  • 8
  • What is the error, is it a divide by 0 error? Does your c/d formula have `WhilePrintingRecords;` in it? – EvilBob22 Mar 27 '13 at 20:53
  • Sorry I left out c/d formula error checking for divide by 0 to simplify the problem (but yes it is there). I haven't got a WhilePrintingRecords statement I will check if that makes a difference. Thanks B – Bryn Mar 28 '13 at 00:04
  • @EvilBob22 - It didn't seem to change the output using WhilePrintingRecords, Please see edit for more clarity – Bryn Mar 28 '13 at 00:29
  • Ahh, I may have been leading you wrong with the `WhilePrintingRecords;`, we probably need to take that out. The issue is that the {numeric field} being used should not be the running total, but the value that is being totaled (the field that was selected in the running total wizard). – EvilBob22 Mar 28 '13 at 15:11

0 Answers0