Problem With Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22478
Printed Date: 03 May 2024 at 4:00am
Topic: Problem With Formula
Posted By: gkohut
Subject: Problem With Formula
Date 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}
|
Replies:
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: gkohut
Date 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
|
Posted By: gkohut
Date 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
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: gkohut
Date Posted: 17 Oct 2017 at 9:40am
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: gkohut
Date Posted: 17 Oct 2017 at 9:45am
Dell,
Just an excel spreadsheet
|
Posted By: gkohut
Date 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`
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: gkohut
Date Posted: 18 Oct 2017 at 7:34am
Dell
Could we not use sub reoports then compare the 2 sub reports as to which do not have a match
|
Posted By: hilfy
Date Posted: 18 Oct 2017 at 10:14am
No. With a subreport, you would need to have it run for each company and you can't use its results for filtering your report.
Where is the data for the spreadsheet coming from?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|