SUBTOTALS for columns?

[kaeserea]

Hello!

I have a report:
[tt]
TABLE FILE XYZ
COUNT PRODUCT
ACROSS MONTH
END
[/tt]
I would now like to have each time when the ACROSS value changes (e.g. from January to February) a subtotal.
The PRODUCT field by the way is a character field A10 which stores the name of the product.
The MONTH field is also character A2.

I tried two things which didn't work:

COUNT PRODUCT WITHIN MONTH
(probably didn't work because PRODUCT is A10)

And RECAP only works with BY sorting....

Does anybody know how to solve this?

Eva

 

[nonskanse]

Are you trying to go
may total june total july total
1 1 2 3 2 5
across the page?

Or are you trying to do something else?

-nonskanse

 

[kaeserea]

Yes, that's it. I'd like to have a summation for each month. It should look like this:

... 30th May | 31st May | Sum May | 1st June | ... | 30th June | Sum June | 1st July | .... | 31st July | Sum July .....

 

[nonskanse]

I don't really have anything for that other than really overdoing defines and computes. If I can think of anything I'll let you know. Hopefully there's someone who knows better.

I wish I could help more!
nonskanse

 

[craigiep]

Eva,

I think you'll need to actually define a new column which will be the total for each month, append it to your original file, and use it just like any other column.

Make sure your date field is alpha (you may have to play around a bit to get a suitable format) and define a new total field

-* filedef the file so you can append the totals to it
FILEDEF XYZ DISK xyz.ftm (APPEND

DEFINE FILE XYZ
-* this could be done using a file of months to be read in by dialogue manager, to make it more flexible - see previous post DEFINE:make column where each row has one day of current month

-* this is the equivalent of your date field
DATE_FIELD/A10 = IF MONTH EQ '01' THEN 'JAN Total' ELSE
IF MONTH EQ '02' THEN 'Feb Total' etc ;
END

TABLE FILE XYZ
SUM PRODUCT
BY DATE_FIELD
ON TABLE HOLD AS XYZ
END

-* this will put the totals into your existing file, so now when you sum across date you'll get your totals.

This is a very simplified example, but hopefully it's enough to give you an idea.

 

[focwizard]

I'm assuming you also have a 'day' as an ACROSS, otherwise the subtotal on MONTH makes no sense. If so, how about using the ACROSS-TOTAL keyword on the 'day' field, like this:

TABLE FILE CAR                       
SUM RCOST BY COUNTRY ACROSS BODYTYPE 
ACROSS SEATS ACROSS-TOTAL            
END

If it's NOT another ACROSS, but a list of fields, then a COMPUTE after the verb objects should work.

 

[kaeserea]

Hello focwizard,

you're right. The problem is that we don't have the right webFocus version here. Our version doesn't support the ACROSS TOTAL. So we now decided to wait until we have WebFocus5.

Regards
Eva