topic Re: MakeDate Fiscal Dates in QlikView

MakeDate Fiscal Dates

abhaysingh — Mon, 31 Aug 2015 05:38:49 GMT

Hi all,

I have Data From Apr to Mar as per fiscal year... I want to define Dates as per Fiscal Month

like below

Apr - 01-01-15

May-01-02-15

jun- 01-03-15

july- 01-04-15

aug- 01-05-15

sep-01-06-15

oct-01-07-15

nov-01-08-15

Dec- 01-09-15

jan-01-10-16

Feb-01-11-16

Mar-01-12-16

Right now I m doing like this makedate(2015,MonthNumber,01)

MonthNumber = 01,02,03,04,05,06,07,08,09,10,11,12

and 01 it is taking Jan instead of Apr.

How I can get above result In term of dates..

Thanks

Re: MakeDate Fiscal Dates

senpradip007 — Mon, 31 Aug 2015 05:45:37 GMT

Try like

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName // Fiscal Calendar Month Name

;

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Re: MakeDate Fiscal Dates

qlikmsg4u — Mon, 31 Aug 2015 05:47:59 GMT

Load *,Month(Addmonths(Date,3))as Month;

Load * Inline [

Date

01-01-15

01-02-15

01-03-15

01-04-15

01-05-15

01-06-15

01-07-15

01-08-15

01-09-15

01-10-16

01-11-16

01-12-16];

Re: MakeDate Fiscal Dates

Mon, 31 Aug 2015 06:05:12 GMT

Hi,

Try using Pick function in load script like:

LOAD Month,

MonthNumber, PICK(WildMatch(Month,'APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB','MAR'),1,2,3,4,5,6,7,8,9,10,11,12) AS MonthNo

FROM

C:\Users\Pooja.Joshi\Documents\Community1.xlsx

(ooxml, embedded labels, table is Sheet2);

And use this MonthNo in your expression: makedate(2015,MonthNo,01)

Re: MakeDate Fiscal Dates

sunny_talwar — Mon, 31 Aug 2015 06:14:35 GMT

Why can't you just do this:

MakeDate(2015, If(MonthNumber <= 9, MonthNumber + 3, MonthNumber - 9), 1)

Re: MakeDate Fiscal Dates

parthakk — Mon, 31 Aug 2015 06:21:42 GMT

Hi Abhay,

U need to create an inline table as shown

LOAD * Inline [

month,fiscal_monthnum

Apr,1

May,2

Jun,3

Jul,4

Aug,5

Sep,6

Oct,7

Nov,8

Dec,9

Jan,10

Feb,11

Mar,12 ];

then u can write code MakeDate(2015,fiscal_monthnum,01) as Date

Re: MakeDate Fiscal Dates

PrashantSangle — Mon, 31 Aug 2015 06:35:34 GMT

Hi,

Use your logic as it is

just Replace below set variable

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

with

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

Regards,

Re: MakeDate Fiscal Dates

sasiparupudi1 — Mon, 31 Aug 2015 06:52:24 GMT

Will it not affect normal months as well?

Re: MakeDate Fiscal Dates

abhaysingh — Mon, 31 Aug 2015 07:02:42 GMT

HI Sunny,

Actually the real scenario is I have Month wise targets in cross table format Not specific format...

Jan to Dec but this is for Fiscal Year Apr to Mar i.e 2015-2016

and My calendar is as Fiscal Year..

In target with the help of I have created months into Number first by month(num(Date#(Month,'MMM'))) as MonthNum,

now I got MonthNum like Jan, feb, mar till Dec..

And When using it into Make Date it is giving me

jan-01-01-15

Feb-01-02-15

Mar-01-03-15

Apr - 01-04-15

May-01-05-15

jun- 01-06-15

july- 01-07-15

aug- 01-08-15

sep-01-09-15

oct-01-10-15

nov-01-11-15

Dec- 01-12-15

bu this MakeDate(2015, If(MonthNumber <= 9, MonthNumber + 3, MonthNumber - 9), 1)

I m getting

01-01-15	oct
01-02-15	Nov
01-03-15	Dec
01-04-15	Jan
01-05-15	Feb
01-06-15	Mar
01-07-15	Apr
01-08-15	May
01-09-15	Jun
01-10-15	July
01-11-15	Aug
01-12-15	Sep

How I can get Apr as 01-04-15 and Jan as 01-10-15

thanks for support

Re: MakeDate Fiscal Dates

abhaysingh — Mon, 31 Aug 2015 07:04:39 GMT

Sry pls consider following output

How I can get Apr as 01-01-15 and Jan as 01-10-16

Re: MakeDate Fiscal Dates

sunny_talwar — Mon, 31 Aug 2015 07:15:01 GMT

See if this one helps:

MakeDate(If(MonthNumber >= 4, 2015, 2016), If(MonthNumber >= 4, MonthNumber - 3, MonthNumber + 9), 1)

Re: MakeDate Fiscal Dates

somenathroy — Mon, 31 Aug 2015 07:52:09 GMT

Hi,

You may try the YearStart() function as below code:

SET vStartDate = '01-Jan-2015';

SET vEndDate = '01-Dec-2015';

DateCal:

LOAD *, YearStart(Date,1,4+RecNo()-1) as FDate;

LOAD

AddMonths(Date(Date#('$(vStartDate)','DD-MMM-YYYY'),'DD/MM/YYYY'), RecNo()-1) as Date

AutoGenerate 12;

Regards,

Som

Re: MakeDate Fiscal Dates

PrashantSangle — Mon, 31 Aug 2015 07:56:43 GMT

Hi,

Have you try my suggestion??

Regards

Re: MakeDate Fiscal Dates

qlikviewwizard — Mon, 31 Aug 2015 08:04:47 GMT

Hi Abhay,

Please change the default month names as shown below. Hope this will help you. Thank you.

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