Need help with "subset" formula

[pjewett]

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

 

[lbass]

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