Fiscal Year Help

[ashfaq_haseeb]

Dear experts

I have A field in a table by name Fiscal year which contains years in JD EDWARDS Environment like

Fiscal Year

0

1

2

3

4

5

6

7

8

9

10

94

95

97

98

99

now i need to create Calender based on this field?

Remember I don't have any date field in my table.

Hoiw would i do that?

Thanks and Regards

ASHFAQ

[syed_muzammil]

Hi,

Try this

Temp:
LOAD
makedate(min([Fiscal Date])) AS minDate,
makedate(max([Fiscal Date]),12,31) AS maxDate
RESIDENT
(The Table you want to load [Fiscal Date] from);

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP TABLE Temp;

TempCalendar:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);

MasterCalendar:
LOAD
TempDate AS OrderDate,
week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Year2date(TempDate)*-1 AS CurYTDFlag,
Year2date(TempDate,-1)*-1 AS LastYTDFlag,
inyear(TempDate,Monthstart($(varMaxDate)),-1) AS RC12,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
applymap('QuartersMap', month(TempDate), null()) AS Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay
RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

[ashfaq_haseeb]

HI

Thats not the case we are looking for. If it is date then your suggestion was right. but that field only contains two digits.

I will elobrate it in more detail.

I have a table with the following fields

LOAD [Fiscal Year],
if([Fiscal Year]>60, 1900+[Fiscal Year],2000+[Fiscal Year]) as Year,
[Cost Center],
Object,
Subsidiary,
[Ledger Type],
[Prior Year End Bal],
Subledger,
[Subledger Type],
[Jan Bal],
[Feb Bal],
[Mar Bal],
[Apr Bal],
[May Bal],
[Jun Bal],
[Jul Bal],
[Aug Bal],
[Sep Bal],
[oct Bal],
[Nov Bal],
[Dec Bal],
[Account Id]
FROM

xyz

where [Fiscal Year], contains numbers as mentioned above in the post

now i manage to calculate year,

need to calculate month and quarter of the same.

also i need to calculate sum bused on this selection.

I don't have gl period but have GL amount for each month stored in separate fields.

Can any one help me on this issue.

Thanks and Regards

ASHFAQ

[ashfaq_haseeb]

Can any 1 help me om this please

[#5]

Hi

I think you can load the table 12 times, one for everry month and put in the month.

Table:

LOAD [Fiscal Year],
if([Fiscal Year]>60, 1900+[Fiscal Year],2000+[Fiscal Year]) as Year,
[Cost Center],
Object,
Subsidiary,
[Ledger Type],
[Prior Year End Bal],
Subledger,
[Subledger Type],
[Jan Bal],
'Jan' as Month,
[Account Id]
FROM

xyz

join(Table)

LOAD [Fiscal Year],
if([Fiscal Year]>60, 1900+[Fiscal Year],2000+[Fiscal Year]) as Year,
[Cost Center],
Object,
Subsidiary,
[Ledger Type],
[Prior Year End Bal],
Subledger,
[Subledger Type],
[Feb Bal],
'Feb' as Month,
[Account Id]
FROM

xyz

And so on for every month.

Anders

[ashfaq_haseeb]

hi all

I manage to get Month nd quarter by below

GLBalanceMain:
LOAD [Fiscal Year],
Year,
[Cost Center],
Object,
Subsidiary,
[Ledger Type],
[Prior Year End Bal],
Subledger,
[Subledger Type],
[Jan Bal],
[Feb Bal],
[Mar Bal],
[Apr Bal],
[May Bal],
[Jun Bal],
[Jul Bal],
[Aug Bal],
[Sep Bal],
[oct Bal],
[Nov Bal],
[Dec Bal],
if([Jan Bal],Text('Jan'),if ([Feb Bal],Text('Feb'),if ([Mar Bal],Text('Mar'),if ([Apr Bal],Text('Apr'),if ([May Bal],Text('May'),if ([Jun Bal],Text('Jun'),if ([Jul Bal],Text('Jul'),if ([Aug Bal],Text('Aug'),if ([Sep Bal],Text('Sep'),if ([oct Bal],Text('Oct'),if ([Nov Bal],Text('Nov'),if ([Dec Bal],Text('Dec'))))))))))))) as Month,
[Account Id]
FROM
xyz

LOAD * INLINE [
Month, Quarter
Jan, Q1
Feb, Q1
Mar, Q1
Apr, Q2
May, Q2
Jun, Q2
Jul, Q3
Aug, Q3
Sep, Q3
Oct, Q4
Nov, Q4
Dec, Q4
];

now i want to calculate Period amount or te selectedmonth.

EG: if year selected = 2010 and MOnth Sleted- JAN FEB and MARCH

then it sould giv me he Periodamount for th Particul seletion

Note: A seperate field with Amoun for that month is stored seperately

how can I acheive this?