Need help with "subset" formula
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: pmjewett — 8 May 2019 at 9:03am
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
Posted By: lockwelle — 8 May 2019 at 12:12pm
well, there are running totals, which is not my forte, or shared variable to do the summing. Both will work.
Shared variable tend to be a group of formulas: initialize, increment, display. If you entire report is going to use the same value, you don't need initialize.
Typically they look something like this:
initialize:
shared numbervar lfy := 0;
shared numbervar fy := 0;
""//hides the assignment.
//put this formula in the group where the values reset
display, pretty simple:
shared numbervar lfy
and another for fy
shared numbervar fy
put those in the section that you want your values displayed.
obviously, the most important formula is the increment
shared numbervar lfy;
shared numbervar fy;
if {parameter} < 1905 then
lfy := lfy + {field}
else
fy := fy + {field};
""//again to hide a value being displayed
//put in the detail section
I think that what is happening is that you have formulas for summing in the same section (probably a footer or header) which means that the value is calculated for the entire group depending on what the value is right then.
HTH
Posted By: DBlank — 13 May 2019 at 4:26am
a few other thoughts...you cannot conditionally sum like this
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})
instead you can do what lockwelle is suggesting, use RTs, both of which will limit how you can sum the data, or do two simple formula's to 'categorize' the data'
//LFYTD
if {TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period}
In '1805' to '1904' then {TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.GROS} else 0
You can now sum this formula field to get the value you want and you can do it at any report level
//FYTD
if {TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.Period}>='1905' then
{TBL_SUMMARY_SALES_CUST_REGION_BRICK_NO_PACKS.GROS} else 0
Posted By: pmjewett — 13 May 2019 at 2:13pm
Thanks to you both. I'm going to look this over first thing in the morning!
|
