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
Edited by pmjewett - 08 May 2019 at 9:04am