MakeDate Fiscal Dates

[abhaysingh]

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

[senpradip007]

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;

[qlikmsg4u]

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];

[#4]

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)

[sunny_talwar]

Why can't you just do this:

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

[parthakk]

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

[PrashantSangle]

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,

[sasiparupudi1]

Will it not affect normal months as well?

[abhaysingh]

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

[abhaysingh]

Sry pls consider following output

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