Store Sales Comparison Report

JamieBrown · 2006-09-07

Hi,

I have searched the forum, but couldn't find anything that relates.

I need to create a comparison report showing store sales by period. For example: the Report could show a stores sales for June for 2005 against 2006. (Ideally on the same line)

Is this possible?

Many thanks in advance.
Jamie

David_Cox · 2006-09-07

Hi Jamie, have a look at the tutorials on my website, it will give you an Idea of how to use the PeriodFormManagement codeunits built in functions to do what you want.

Many GB retailers use non standard Periods, so just going back 12 months may not work, the GB version uses different functions to the worldwide version, this will allow for 4-4-5 Accounting for Retail, you can download the report and pdf from my downloads page as well.

http://www.adeptris.com/Dynamics/Tutorials/DynamicDateReport/tabid/72/Default.aspx

Hope this helps

Kowa · 2006-09-07

If each store represents a location you can use the following method with a location filter applied to the item table :

Take a look in the OnPreReport Trigger of Report 712. A second ItemStatisticsBuf record variable is needed ItemStatisticsBufLastYear to which the last years period date filter can be applied. Use COPYFILTERS first to copy all other filters from ItemStatisticsBuf, then modify the date filter :

You can calculate the period for the last year with code like this:

FirstDay := GETRANGEMIN("Date filter");
LastDay := GETRANGEMAX("Date filter");
FirstDayLastYear := CALCDATE('<-1Y>',FirstDay);
LastDayLastYear := CALCDATE('<-1Y>',LastDay);
ItemStatisticsBufLastYear.SETRANGE(LastYearDateFilter,FirstDayLastYear,LastDayLastYear);

Use the ItemStatisticsBufLastYear Variable and correspondingly

SalesQtyLastYear
SalesAmountLastYear
COGSAmountLastYear
ItemProfitLastYear

in the Calculate Function to calculate the values for the last year (basically, make a copy of each function and change the ItemStatisticsBuf to ItemStatisticsBufLastYear)

Edit 24 Nov 2015: Code snippet battered by forum upgrade

David_Cox · 2006-09-07

In the UK many Retail Customers use 4-4-5 Accounting, this gives fifty two weeks and twelve Periods, that start on the same day of the week, four thirteen week quarters of 91 days, so the financial year start moves each year as there are always 364 days in the finacial year, this is so the same period the previous year starts on the same day and has equal number of days, which is critical for accounting projections.

I am sure this is a quirk of UK auditors, so the CALCDATE('-1Y',myDate), is not suitable, there is in the GB version code to deal with this and this should be considered for Store reporting, we have user definable Weeks, Periods, Quarters and Years, as some Customers want the weeks to run from a different day maybe a wednesday, period 1 could be 1st to the 28 and period 2 starts on the 29th of the same month.

I hope this helps explain the UK, Retail accounting requirments, to the forum users from other lands, thus why suggested using the PeriodFormManagement Codeunit for GB requirements.

David

Savatage · 2006-09-07

Here's a post from Tip's N Tricks about Colums using arrays.

I have used this successfully and I think it can easily be modified to compare two months a year apart.

Maybe changing the array to 13 and only showing [1] & [13]
Note; Also easily changable for use for Vendor Purchases too..

http://www.mibuso.com/forum/viewtopic.php?t=10082

Here's my report

http://www.geocities.com/navision_attai ... yMonth.txt

(Edit) - Ooops I should have read the previous post :whistle:

David_Cox · 2006-09-07

Pressed Quote instaed of edit :oops:

David_Cox · 2006-09-07

:oops: Twice !!

David Cox wrote:

In the UK many Retail Customers use 4-4-5 Accounting, this gives fifty two weeks and twelve Periods, that start on the same day of the week, four thirteen week quarters of 91 days, so the financial year start moves each year as there are always 364 days in the finacial year, this is so the same period the previous year starts on the same day and has equal number of days, which is critical for accounting projections.

I am sure this is a quirk of UK auditors, so the CALCDATE('-1Y',myDate), is not suitable, there is in the GB version code to deal with this and this should be considered for Store reporting, we have user definable Weeks, Periods, Quarters and Years, as some Customers want the weeks to run from a different day maybe a wednesday, period 1 could be 1st to the 28 and period 2 starts on the 29th of the same month.

I hope this helps explain the UK, Retail accounting requirments, to the forum users from other lands, thus why suggested using the PeriodFormManagement Codeunit for GB requirements.

David

This code is taken from Cronus UK Customer Statistics and has been altered to give you what you want.

Finds Curent period then back 12 Periods

IF CurrentDate <> WORKDATE THEN BEGIN
CurrentDate := WORKDATE;
Calendar."Period Start" := CurrentDate;
PeriodFormMgt.SetCalendarSource(CalendarSource);
PeriodFormMgt.FindDate('',Calendar,2);
CustDateFilter[1] := PeriodFormMgt.BuildDateFilter(Calendar);
CustDateName[1] := Calendar."Period Name";
PeriodFormMgt.NextDate(-12,Calendar,2);
CustDateFilter[2] := PeriodFormMgt.BuildDateFilter(Calendar);
CustDateName[2] := Calendar."Period Name";
END;

JamieBrown · 2006-09-13

Thanks for all your help!

David,

I've got a copy of your tutorial and have started working through it. (Is proving extremely useful)!.

Kowa,

I used your example to get the report working, although as David said we need to use 4-4-5 longterm.

Thanks again.
Jamie

Store Sales Comparison Report

Comments

Categories