×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help with "subset" formula

Need help with "subset" formula

Need help with "subset" formula

(OP)

Hello,

I'm trying to build a very simple report that will compare FY sales year over year but getting strange results and wonder if I'm doing something wrong.

Example
Acct# Name LFYTD FYTD
08091 Bob's $68,366 $0

Report Filter
({TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period} >= "1805" and {TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period} <='1905')

The LFYTD formula field
if ({TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period} In '1805' to '1904') then
SUM({TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.GROS},{TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.CUST})

The FYTD formula field
if {TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period}>='1905' then
SUM({TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.GROS},{TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.CUST})

No matter what I've tried the LFYTD field is including sales from the period 1905 despite the fact that it is out of my range in the formula.

FYTD consistantly shows "0" despite that fact that the example account has sales in the period 1905 which is within range of my formula.

Results should be..
Acct# Name LFYTD FYTD
08091 Bob's $64,655 $3,712


What am I missing or am I going about this wrong?

Thanks in advance

RE: Need help with "subset" formula

Your formulas are saying: if <this condition> then show me the sum of all sales for each customer. For a conditional amount, instead set up your formulas like this:

If table.period <1905 then {table.gros}

Then insert a sum on the formula at the group level. The formula will only capture the sales that meet the condition.

I don’t understand what your period field stands for. Based on your selection formula, it looks like you should get results for 1905. Using YOUR formulas, you should get the same result for both formulas (the total of all sales for that customer). I would place the period field in the detail section and see if 1905 appears. What does 1905 represent? If the period field represents a datetime, then it is possible that any periods containing a time for 1905 are being excluded, since a datetime would interpret a date as having time(0,0,0). Try changing your selection formula to use < 1906 for the period end, and see if that then captures your 1905 data.

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close