Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Need help with "subset" formula Post Reply Post New Topic
Author Message
pmjewett
Newbie
Newbie


Joined: 28 Nov 2016
Online Status: Offline
Posts: 8
Quote pmjewett Replybullet Topic: Need help with "subset" formula
    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





Edited by pmjewett - 08 May 2019 at 9:04am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4282
Quote lockwelle Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8991
Quote DBlank Replybullet 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
IP IP Logged
pmjewett
Newbie
Newbie


Joined: 28 Nov 2016
Online Status: Offline
Posts: 8
Quote pmjewett Replybullet Posted: 13 May 2019 at 2:13pm
Thanks to you both. I'm going to look this over first thing in the morning!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.