Discussion board where members can get started with QlikView.
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?.
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:
If(Week < 27, Week +26 , Week -26) as WeekCount
Now you have a field (WeekCount) which start counting at week 27If you sort on Expression SUM(WeekCount) it get the result you want.
PS If you dont have a Week field add the following line to your script:
week(DateField) AS Week,