4,818
Hi expert, I want to create a report for the customers who haven't purchased for 30 days, 60 days, 180 days, and 1 year etc. Currently my report is working, but the problem is 30 days record also includes those who haven't purchased for over 30 days such as over 2 years. This is not accurate. How to exactly retrieve those records, not roughly? Thank you in advance!
*This post is locked for comments
"30 days record also includes those who haven't purchased for over 30 days such as over 2 years. This is not accurate."
As I have mentioned, (assuming you've done your report properly) the result you are getting IS accurate because the customer has also not purchased in those earlier time frames. This means that if the customer hasn't ordered in the 90 day time frame then they also haven't ordered within the 30 day time frame. Does that make sense?
Your approach to this report may need to be altered for you to get the results you are looking for. The logic in the SQL I gave you above can work both ways - it can show customers who haven't ordered during the time frames OR it can show customers who have ordered during those time frames - to alter the result you simply change the 'IN' statement to a 'NOT IN' statement as shown below and that reverses the result.
Whether you are comfortable working with SQL or not the logic in the code I gave you before represents that kind of logic you'll need to employ in any language to make this work.
As a side note, any reports you code in any application language that pull data from a database will ultimately use SQL to query the database... SQL is the language that queries the SQL Server behind your NAV application.
The SQL code I provided in the earlier post shows the kind of logic you will need to employ, and also how you'll most likely need to use a series of CASE statements with boolean operators to define the time frame buckets.
Share
Report
All responses (
Answers (
----RESULTS FOR CUSTOMERS WHO ORDERED DURING THE TIME FRAME----
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -60 , GETDATE()) AND DATEADD(day , -31 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 31-60 Days],
----RESULTS FOR CUSTOMERS WHO HAVE NOT ORDERED DURING THE TIME FRAME----
CASE
WHEN [C].[No_] NOT IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -60 , GETDATE()) AND DATEADD(day , -31 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 31-60 Days],
Refer below links :
Video : https://msdn.microsoft.com/en-us/dynamics/nav/dn783472.aspx
Designing Reports for the RoleTailored Client : https://msdn.microsoft.com/en-us/library/dd355370.aspx
Designing a Customer List Report : https://msdn.microsoft.com/en-us/library/dd355179.aspx
Designing a Customer Sales Order Report : https://msdn.microsoft.com/en-us/library/dd339014.aspx
Creating a Link from a Report to a Page : https://msdn.microsoft.com/en-us/library/dd301304.aspx
Walkthrough: Designing a Report with Images, Interactive Sorting, and Visibility Toggle : https://msdn.microsoft.com/en-us/library/dd301353.aspx
To develop a report you need to have developer license for Microsoft Dynamics NAV and knowledge on the C/AL development. Report you are planning to develop is bit complex and might need some support.
If you have the developer license let us know, we can guide you to some extent.
You need to use from date and to date so try this
CustLedgEntry.SETRANGE("Posting Date",CALCDATE('<-30D>',TODAY),TODAY);
The problem you are facing is that for some customers it is true that they have not purchased in the last 30 days, AND they have not purchased in the last 60 days, AND they have not purchased in the last 180 days... which causes that customer to appear in each of the time frames.
It's easy to do a report that shows what customers haven't ordered in the last 30 days, the last 60 days, OR the last 90 days (one bucket), however even when I work out the logic to have the customer names only appear (if they haven't ordered) in ONLY one of these columns (30, 60, 90, 180, 365) the report doesn't look right because for example it would show the customer name in the 90 day column but nulls in the 30 and 60 day column though they also haven't ordered in the 30 and 60 day time frames.
In all practicality, instead it may be better to have the name of the customer appear in the columns (30, 60, 90, 180, 365) for when they HAVE placed an order. Then you can infer which customers have not ordered for specific time frames by the null values that will show in the columns for which they haven't ordered.
If you have Visual Studio or can run this query in SQL Management Studio then you can see an example of what I'm referring to with the following query:
Note: *Replace the XXXXXX with the prefix of your NAV production database table names
This screen shot above shows when the customer last ordered. NULL values indicate they have not ordered within the last 30/60 days. Here is the T-SQL code for this:
SELECT DISTINCT
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -30 , GETDATE()) AND GETDATE()
) THEN [C].[Name]
END AS [Purchased 0-30 Days],
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -60 , GETDATE()) AND DATEADD(day , -31 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 31-60 Days],
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -90 , GETDATE()) AND DATEADD(day , -61 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 61-90 Days],
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -180 , GETDATE()) AND DATEADD(day , -91 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 91-180 Days],
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] BETWEEN DATEADD(day , -365, GETDATE()) AND DATEADD(day , -181 , GETDATE())
) THEN [C].[Name]
END AS [Purchased 181-365 Days],
CASE
WHEN [C].[No_] IN
(
SELECT [Sell-to Customer No_]
FROM [XXXXXX$Sales Invoice Header]
WHERE [Order Date] > DATEADD(day , -365, GETDATE())
) THEN [C].[Name]
END AS [Purchased 365+ Days]
FROM [XXXXXX$Customer] [C]
Use CALCDATE function to calculate the date
msdn.microsoft.com/.../dd301368.aspx
CALCDATE('<-15D>',TODAY)
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
