Hello,

I have an excel report which has multiple sortfields - I am trying to further group by one of the sortfields based on a condition.

For example: I have a category where the amounts are either in US dollars or Canadian dollars - I am trying to separate the records with USD from those with CAD only when they fall in a particular category and not any other.

So I have categories: A,B,C,D and each one has records with amounts in both USD & CAD; but I am looking to separate records with USD & CAD only when category is A and not for others.

How can I do a conditional BY statement? Or is there another way to achieve this?

Thanks in advance!

This message has been edited. Last edited by: Nova27,

Define a new column that determines the value based on the logic you just described. It'll look something like this:

 
NEW_AMOUNT/D12.2= IF CATEGORY EQ 'A' THEN AMOUNT ELSE 0; 
 

Ok I apologize, I think I didn't explain it correctly...

The currency code (USD or CAD) is the field which I have to separate based on the catergory.

For example:
The report looks like this-

CATEGORY A
Customer    Customer Name   Category  Trans  Amount   Currency   Country   Acct Num   Date
1111         test1            A         CR     10        USD       US       657869    08/01
3333         test3            A         CR     50        CAD       CA       55768     07/01
Total CR:                                      60

2222         test2            A         DB     15        USD       US       476657    07/30
Total DB:                                      15

CATEGORY B
Customer    Customer Name   Category  Trans  Amount   Currency   Country   Acct Num   Date
1111         test1            B         CR     10        USD       US       657869    08/01
Total CR for USD:                              10

3333         test3            B         CR     50        CAD       CA       55768     07/01
Total CR for CAD:                              50

2222         test2            B         DB     15        USD       US       476657    07/30
Total DB for USD:                              15

SO my BY statements are:

BY CATEGORY NOPRINT
BY TRANS NOPRINT
.......
ON CATEGORY PAGE-BREAK
ON TRANS SUBTOTAL
........
........

I need to group by based on currency code when category is B - please advise!

Is this may answer your need ?

TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
WHEN COUNTRY EQ 'ITALY' OR 'JAPAN';
END

This might help:

 
DEFINE FILE xxxx
BCURR/A3=IF CATEGORY EQ 'B' THEN CURRENCY ELSE ' ';
...
END
TABLE FILE xxxx
....
BY CATEGORY NOPRINT
BY TRANS NOPRINT
BY BCURR NOPRINT
ON BCURR SUBTOTAL WHEN BCURR NE ' '

 

Thanks a lot Danny.. Your solution worked

Thank you everyone for helping me out with this.

Nova, you're welcome.