Print Page | Close Window

Need help with "subset" formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22769
Printed Date: 01 May 2024 at 6:51pm


Topic: Need help with "subset" formula
Posted By: pmjewett
Subject: Need help with "subset" formula
Date Posted: 08 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


Replies:
Posted By: lockwelle
Date Posted: 08 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
Date Posted: 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
Date Posted: 13 May 2019 at 2:13pm
Thanks to you both. I'm going to look this over first thing in the morning!


Print Page | Close Window