topic Fiscal in QlikView

Fiscal

Tue, 03 Aug 2010 08:18:50 GMT

Hey guys

I got a set of dates here but the customer would like it as a fiscal calender starting from 1July-30June. It needs to be set up as default for the whle document without using a calender object.

can anyone please instruct me on how to go about dong this?

Any help is much appreciated.

Thanks

Fiscal

Tue, 03 Aug 2010 09:46:32 GMT

when loading the script use the following, this sets your year to begin on the 7th month

yearname ( Date, 0, 7 ) as FiscalYear,

Fiscal

Tue, 03 Aug 2010 10:29:04 GMT

My script starts like this:

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='$ # ##0,00;$-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Now where must i put in what you said?

Fiscal

Tue, 03 Aug 2010 10:39:59 GMT

you can do one of the following

When loading your table add the following script

LOAD
order_no,
order_date,
yearname (order_date, 0, 7 ) as FiscalYear,
customer,
etc... ;
SQL Select *
FROM TABLE Orders;

2.
What I do is create a seperate date table and link my orders table to this. This gives me greater flexibility when I want to manipulate dates

Date:
load
today()-recno()+1 as Date
autogenerate(today()-'2004-12-31');

DateParts:
load
Date as order_date,
Year(Date) as Year,
yearname ( Date, 0, 4 ) as FiscalYear,
weekstart(Date) as WeekStart,

Month(Date)&' - '&right(year(Date),2) as MonthYear,
Month(Date) as Month,
Day(Date) as Day,
Week(Date) as Week,
Weekday(Date) as WeekDay
resident Date;

drop table Date;

Fiscal

Tue, 03 Aug 2010 10:43:15 GMT

Im very sorry but i should mention i am extremely new to qlikview and i got no idea what ur talking about.

can you maybe explain a bit more in detail step by step?

Thanks so much

Fiscal

Tue, 03 Aug 2010 10:45:26 GMT

No problem, can you load the qv document or just post the load script of the table that contains the date field.

Fiscal

Tue, 03 Aug 2010 12:11:43 GMT

Sorry but once again im not entirely sure what you mean.... the raw data with the dates contained is in a access database file and looks like this:

and it continues with thousands of records until December 2010.

Fiscal

Tue, 03 Aug 2010 12:19:04 GMT

In the EDIT Script or LOAD script, can you show me the load script / code, not the end result e.g.

Select
order_no,
order_date,
customer
From table OrdersH;

Fiscal

Tue, 03 Aug 2010 12:44:55 GMT

See attached example document

Fiscal

Tue, 03 Aug 2010 12:47:13 GMT

i see. its:

LOAD BusinessEntityID,

TerritoryID,

StartDate,

EndDate;

Fiscal

Tue, 03 Aug 2010 13:11:21 GMT

So use the following script to make the startdate into fiscal year from Jul-Jun.....

LOAD
BusinessEntityID,
TerritoryID,
StartDate,
yearname (StartDate, 0, 7) as FiscalYear,
EndDate;

Fiscal

Tue, 03 Aug 2010 13:18:01 GMT

thanks for explaining it. much appreciated

Fiscal

Tue, 03 Aug 2010 13:33:06 GMT

Hi carneyfm,

We have the almost similar Requirement but like Jan 15 to Feb 15 will be considered as one month.

How to get the same.

Regards,XXX

Fiscal

Tue, 03 Aug 2010 13:50:23 GMT

Unfortunately I don't have a simple solution. If your month always begins on the 15th try yearname (StartDate, 15, 7) as FiscalYear.

I too have a similar need to split months and what I have done is created an excel workbook with a list of all dates, I link these dates to my orders table.

I then use the Year_Period column as a reference in the reports

OrderH:
order_no,
order_date,
order_date as JJ_Date,
customer

.....
From Table orders;

SJ_Date:
NOCONCATENATE LOAD
JJ_Date,
[Year_Period] as SJ_Year_Period
FROM [\\Peiintdub1\qlikviewreports\Qlikview_Excel\Qlikview\SJ Dates.xls] (biff, embedded labels, table is [Sheet2$]);