(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Multi-year sales comparison
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Multi-year sales comparison (Read 247 times)
cbislander
Full Member
***
Offline



Posts: 117
Joined: Mar 22nd, 2018
Multi-year sales comparison
Mar 12th, 2025 at 12:38am
Print Post Print Post  
I have a database that processes reports from other databases.  One routine prints reports for a specific date range.

If I wanted to compare sales for March 1st to March 11th, it would produce 5 different reports from 2020 to 2025.  This works by printing from each year's database or a Master database that has all the records.  I also wanted to have this done on one report, but I have not found the way to do it.  I have attached what the program looks like to generate the reports.
  
MULTI-YEAR_COMPARISON.txt ( 3 KB | 7 Downloads )
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2492
Joined: Aug 20th, 2003
Re: Multi-year sales comparison
Reply #1 - Mar 12th, 2025 at 2:23pm
Print Post Print Post  
Depending on how your report is setup you can get 1 big result set by doing a retrieve like

Code
Select All
vTest5 = "!DATESTAMP=" + v2025 + ";" + v2024 + ";" + v2023 + ";" + v2022 + ";" + v2021 + ";" + v2020


And then sort the XResultset by DateStamp. And have the report do a break by Year of Datestamp(Break Value of Y) to separate the years of data.

btw it would also be better to write this to be dynamic by using @Year() so it doesn't need to be updated every year. something like:
Code
Select All
Var vThisYear as String
Var vLastYear as String
Var vTwoYearsAgo as String

vThisYear = vTest2 + ".." + vTest7
vLastYear =  @Str(@Year(vTest2)-1) + @RIGHT(vTest2,6) + ".." + @Str(@Year(vTest2)-1) + @RIGHT(vTest7,6)
vTwoYearsAgo =  @Str(@Year(vTest2)-2) + @RIGHT(vTest2,6) + ".." + @Str(@Year(vTest2)-2) + @RIGHT(vTest7,6)


-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
cbislander
Full Member
***
Offline



Posts: 117
Joined: Mar 22nd, 2018
Re: Multi-year sales comparison
Reply #2 - Mar 12th, 2025 at 4:57pm
Print Post Print Post  
Hi Ray,

This is what I used:


vYears = "!DATESTAMP=" + v2025 + ";" + v2024 + ";" + v2023 + ";" + v2022 + ";" + v2021 + ";" + v2020


vRSHandle = @XResultSetSearch("DATA\MASTER.DB", "CUSTOMER", SEARCH_MODE_AND, SEARCH_SYNTAX_QA,vYears)

vTest8 = "YEARLY " + vTest1


vReport=@xresultsetprintreport(vTest8 ,vRSHandle,1)
WriteLn("Done")
XResultSetClose(vRSHandle)}

When I run it, the yearly results don't include anything before 2025.
  
salescomp.png ( 37 KB | 5 Downloads )
salescomp.png
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2492
Joined: Aug 20th, 2003
Re: Multi-year sales comparison
Reply #3 - Mar 12th, 2025 at 7:53pm
Print Post Print Post  
If you writeln out vYears, and then paste everything after DateStamp= into the Datestamp element at the retrieve spec and press f10, do you get records for all years?

If you writeln out @XResultSetTotal() of vRSHandle, do you get more than the number of 2025 records?

If both of those are a positive result then the issue is with either the programming or design of your report.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
cbislander
Full Member
***
Offline



Posts: 117
Joined: Mar 22nd, 2018
Re: Multi-year sales comparison
Reply #4 - Mar 13th, 2025 at 12:59am
Print Post Print Post  
I tried different formats for the dates, but nothing worked.  I then thought about order of the Search. As shown, below, my searches would only bring up the 2025.

!DATESTAMP=2025/03/01..2025/03/12;2024/03/01..2024/03/12;2023/03/01..2023/03/12;
2022/03/01..2022/03/12;2021/03/01..2021/03/12;2020/03/01..2020/03/12

Still the yearly report is just bringing 2025.  I tried changing my Date format in the search to 3/1/2025..3/12/2025 and 3-1-2025..3-12-2025 .

  I decided to start from 2020 and go to 2025 and it worked.

!DATESTAMP=2020/03/01..2020/03/12;2021/03/01..2021/03/12;2022/03/01..2022/03/12;
2023/03/01..2023/03/12;2024/03/01..2024/03/12;2025/03/01..2025/03/12
« Last Edit: Mar 13th, 2025 at 12:30pm by cbislander »  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2025. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge