Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Financial Year, Quarter, Month, Week and Weekday script...

Hi all..

Can anyone help me with the following please?:

Question:

You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day

Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

Then confirm the Financial Year, Quarter, Month, Week and Weekday for the following date: 15/06/2011

Thanks

Mertin

3 Replies
er_mohit
Master II
Master II

try this

LET vDateMin = Num(MakeDate(2011,2,28));

  LET vDateMax = Floor(MakeDate(2011,6,15));

  LET vDateToday = Num(Today());

     TempCalendar:

  LOAD

  $(vDateMin) + RowNo() - 1 AS DateNumber,

  Date($(vDateMin) + RowNo() - 1) AS TempDate

  AUTOGENERATE 1

  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

  LOAD

Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,

        Day(TempDate) AS CalendarDayOfMonth,

  WeekDay(TempDate) AS CalendarDayName,

Month(TempDate) AS CalendarMonthName,

  'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'FY' & Right( Year(TempDate),2) as YearQtrfy,

YearName(TempDate,0,2)as FiscalYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

     DROP TABLE TempCalendar;

join with your date field table

revathy_a
Contributor III
Contributor III

hi

You can map the mastercalender and by using the functions like below

         

Date(TransDate) as Dates,

Month(TransDate) as MonthNumber,

MonthName(TransDate) as MonthName

QuarterStart(TransDate),

Week(TransDate),

WeekName(TransDate)

the data in terms of month , week, date and many other  can be obtained.

Regards

Revathy

Not applicable
Author

Hi, logically I would like to do something like the following, except that I need it in the Qlikview syntax, if you

could help me with that please?

TestDate = 15/06/2011;

StartMonth = 3;

EndMonth = 2;

CalYear = Year(TestDate);

CalMonth = Month(TestDate);

FinYear = CalYear + 1;

if CalMonth < StartMonth then FinYear = FinYear - 1;

FinMonth = CalMonth - EndMonth;

if CalMonth < StartMonth then FinMonth = FinMonth + 12;

if FinMonth in (1,2,3) then Quarter = 1;

else if FinMonth in(4,5,6) then Quarter = 2;

else if FinMonth in(7,8,9) then Quarter = 3;

else Quarter = 4;

Days_Between = TestDate - mdy(EndMonth,28,FinYear-1);

Week = floor(Days_Between / 52.1775);

WeekDay = WeekDay(TestDate);