Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Problem With Formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Topic: Problem With Formula
    Posted: 12 Oct 2017 at 10:13am
I am trying to find away to write a formula that will give me the number of NEW customers in the current months and/or previous months.

my logic was using the invoice created date and company

created is less than the current date and them see if there is an invoice for the company in the other dates.

{Created} > DateTime (2017,10,06) and ({@S2}) <> {Company})

{Created} is Invoice Date
{Company} is Company
{@S2} is a formula that contains {Company}


Edited by gkohut - 12 Oct 2017 at 12:01pm
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Oct 2017 at 9:02am
Hi gkohut,

If I understand you correctly, you are defining a new customer as one who has not had any previous orders. Is that correct?

How are you filtering your report? In other words, what do you have in the Select Expert? Are you filtering by dates at all?

-Dell
IP IP Logged
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Posted: 17 Oct 2017 at 9:09am
Dell,

I have it Grouped By Company Then Created.

In theory it is real simple . . .How many new customer in the current month, but I just can't get my head around it to make it happen
IP IP Logged
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Posted: 17 Oct 2017 at 9:11am
eventually  I would like to have a chart in my summary page at the report to show whether we are growing new customers
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Oct 2017 at 9:22am
So you're looking at all of the invoices in the database to determine whether a client is "new"?

-Dell
IP IP Logged
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Posted: 17 Oct 2017 at 9:40am
Dell

Yes, Correct
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Oct 2017 at 9:43am
This is going to be more easily done using a command (SQL Select statement) instead of linking tables in the report. What type of database are you connecting to? If you would like help with the command, please post the SQL that Crystal has generated for the report.

-Dell
IP IP Logged
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Posted: 17 Oct 2017 at 9:45am
Dell,

Just an excel spreadsheet
IP IP Logged
gkohut
Newbie
Newbie


Joined: 12 Oct 2017
Location: Canada
Online Status: Offline
Posts: 7
Quote gkohut Replybullet Posted: 17 Oct 2017 at 9:50am
 SELECT `'Hoja_1_'`.`Created`, `'Hoja_1_'`.`Company`, `'Hoja_1_'`.`Total`, `'Hoja_1_'`.`Profit`, `'Hoja_1_'`.`Customer`
 FROM   `'Hoja 1$'` `'Hoja_1_'`
 WHERE  `'Hoja_1_'`.`Customer`<>'gkohut@hotmail.com' AND  NOT (`'Hoja_1_'`.`Company`='Advantek Lighting Inc.' OR `'Hoja_1_'`.`Company`='Lumisolution')
 ORDER BY `'Hoja_1_'`.`Company`

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Oct 2017 at 9:55am
You can't write a command against an Excel spreadsheet, unfortunately. How does the data get into the spreadsheet?

-Dell
IP IP Logged
Page  of 2 Next >>
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.