Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Conditional Counting Post Reply Post New Topic
Author Message
Chase
Newbie
Newbie


Joined: 28 Jan 2013
Online Status: Offline
Posts: 17
Quote Chase Replybullet Topic: Conditional Counting
    Posted: 28 Jan 2013 at 8:26am
New here and new to crystal but hope to learn fast and contribute where I can. I've used crystal for a grand total of about two weeks.
 
I'm trying to find  away to count discretely based on the count of each unique value. I have a field that has the date for each piece of data. What I am trying to do is isolate the number of days in which there are more than 5 appearances.
 
Let's assume its sales data (its not) and I had data for each sale. What I want to do is break down the number of days in which we had more than 5 sales.
 
Any idea on how I'd do that?
 
In pseudocode I guess I'd be trying to do something like this from what I remember of basic.
 
x = 0
for day in (start_date to end_date)
if count of day > 5
return
x = x + 1
else
end
 
So if in a given week we'd had:
 
Monday: 3 sales
Tuesday: 4 sales
Wednesday 6 sales
Thursday: 7 sales
Friday: 9 sales
 
The result of the formula would be 3.
 
or something like that, but no idea how to syntax it. Can someone help me out? Maybe I'm thinking about this wrong.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jan 2013 at 9:40am
group on the date field set to day
insert a count of a distinct field (like salesid) at the group level
create a Running Total (or 3 shared variable formulas if you want)
 
RT
name=whatever
field to sumamrize=date field
type =distinct count
evaluate=use a formula
count(salesid,date)>5
reset=never
place in report footer
IP IP Logged
Chase
Newbie
Newbie


Joined: 28 Jan 2013
Online Status: Offline
Posts: 17
Quote Chase Replybullet Posted: 31 Jan 2013 at 3:38am
It seems to work perfectly, but I'm having difficulty pulling that amount into a summary. Apparently running totals can't be summarized...is there a way to pull the maximum amount of the running total?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Jan 2013 at 4:25am

you cannot summarize a summary using a RT (or a summary function).

You could use shared variable formulas to work through the summarized counts and shoud the max value like that.
IP IP Logged
Chase
Newbie
Newbie


Joined: 28 Jan 2013
Online Status: Offline
Posts: 17
Quote Chase Replybullet Posted: 31 Jan 2013 at 5:18am
I honestly didn't expect this to be so complicated. Not that I don't like complicated things, but excel has spoiled me. countif(Range,">5")
 
Is there really no way to simply write a formula to go through each value of the day field, count the number of distinct loads in that field, if its over 5, create a tally, then spit out the number at the end, all self-contained in the formula?
 
I would think it would be possible using a for statement of some kind. I just can't seem to get the syntax right.


Edited by Chase - 31 Jan 2013 at 5:20am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Jan 2013 at 6:01am
have at it but I do not think so.
Brian's book has a very good explanation of how crystal uses data passes to build the report, what occurs on each pass, and how you can use that information to get a display problem solved.
Crystal does not create new data or alter your data set . It only displays data. You can get make it look like it is altering data but it is not. I know Crystal annoys a number of people as it seems too limited but I also think these people are trying to make it be a data management tool, not a data display tool.
Many folks use their source to manipulte the data before pulling it in crystal to do what you are trying to do (e.g. using sql stored procedures).
 


Edited by DBlank - 31 Jan 2013 at 6:02am
IP IP Logged
Chase
Newbie
Newbie


Joined: 28 Jan 2013
Online Status: Offline
Posts: 17
Quote Chase Replybullet Posted: 31 Jan 2013 at 7:30am
I'm sure you're right. I don't really know much about crystal.
 
I've spent like two hours trying to get this formula to work so will just give up now.
 
NumberVar i := 0;


for i := 0 to 100000 do
(
if count({table.item},{table.date}) > 5 then
i := i + 1;
);
i;


Edited by Chase - 31 Jan 2013 at 7:30am
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.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.