Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, and thanks in advance for your support.
My company has a tax year from July to June, so I need that any monthly chart will start in July or if the chart is in weeks, in the week 27. How to organize this?.
Best Regards
You can do this by defining a master calendar table already in the script. In the following scriptlet you will find one way to define your fiscal year:
Let vStartOfFiscalYear = 7 ; // July (7) is the first month of the fiscal year
Load distinct
TransactionDate,
Month(TransactionDate) as Month,
Year(TransactionDate) as Year,
Date(MonthStart(TransactionDate),'MM/YYYY') as YearMonth,
Dual(Month(TransactionDate), Mod((Month(TransactionDate)-$(vStartOfFiscalYear)),12)+1) as TaxMonth,
Year(AddMonths(TransactionDate,1-$(vStartOfFiscalYear))) & '/' & Year(AddMonths(TransactionDate,13-$(vStartOfFiscalYear))) as TaxYear
;
Load Date(MakeDate(2011)-1+recno()) as TransactionDate
autogenerate 732;
You will need to have a similar load statement, but you should probably consider loading everything from the transaction table:
Load distinct
TransactionDate,
…
resident TransactionTable;
Here is another way:
Add this to your calender script:
JOIN (Master_Calendar)
Load
Week,
If(Week < 27, Week +26 , Week -26) as WeekCount
Resident Master_Calendar;
Now you have a field (WeekCount) which start counting at week 27
If you sort on Expression SUM(WeekCount) it get the result you want.
Good Luck,
PS If you dont have a Week field add the following line to your script:
week(DateField) AS Week,