Sales Totals

[bv0719]

I was just reading this post (

http://www.tek-tips.com/faqs.cfm?fid=6524).

Noticed that the author says to use Running Totals only if necessary.

I use running totals in several of my reports because I need to see the sales for a current month/year vs a prior month/year.

Is there another way I should accomplish the task without using RT's?

My reports tend to be based on multiple groupings.

Example:
Grp H1 - Warehouse
Grp H2 - Merchandise Class
Grp F3 - Item

MTD Sales by Item | LMTD Sales by Item | Variance | Weight Sold

Actually, another option would be great since I lose the ability to run a TopN on the totals.

Thoughts?

Thanks,

BV

 

[lbass]

Do you have row inflation, i.e., repeating values? If not, you could use conditional formulas instead like:

//{@MTD}:
if {table.date} in MonthToDate then {table.sales}

//{@LMTD}:
if {table.date} in dateserial(year({currentdate}), month(currentdate)-1,1) to dateserial(year({currentdate}), month(currentdate),1)-1 then {table.sales}

You can then insert summaries on these at the various group levels, and use formulas for the variances at each group level.

-LB

 

[bv0719]

Thanks LB. This looks like another solution you helped me with last year. I'm using this logic on a monthly report. It works although I'm still a little fuzzy on the logic.

Not sure how to answer 'repeating values'. Each line on a report will be specific to an item, customer, class, etc.

Thanks,
BV

 

[lbass]

Sometimes table joins result in repeating values that really should only be summarized once. For example:

A 123 B 345
A 123 B 892
A 456 B 345
A 456 B 892

579 1237 Desired Totals

In the conditional formulas approach, the formulas return 0's if the condition isn't met, so you can sum these across various conditions and still get the correct answer. Use care with counts, though. The conditional formulas should end with "then 1" and the summary should be a sum, NOT a count, since a count would tell you only how many times the formula executed.

-LB

 

[bv0719]

LB,

Thanks for the response. I'll give that a try.

If I get repeating values that's often fixed by changing to a left or right outer join. Either way, it's the joins.

Thanks,
BV