QlikView - Fiscal Year Calendar

[Anonymous]

Hi Guys, Good Afternoon!

The following Master Calendar is working very fine for Classic Calendar purpose:

Trace ==========================;
Trace Load TAB "Master Calendar";
Trace --------------------------;

QuartersMap:
Mapping Load
RowNo() as Month,
'Q' & Ceil(RowNo() / 3) as Quarter
AutoGenerate(12);

Temp:
Load
Min([Calendário Mestre.Data]) as minDate,
Max([Calendário Mestre.Data]) as maxDate
Resident [Minha Tabela];

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

[Calendário Mestre]:
Load
Year(TempDate) as [Calendário Mestre.Ano],
Month(TempDate) as [Calendário Mestre.Mês],
Day(TempDate) as [Calendário Mestre.Dia],
ApplyMap('QuartersMap', Month(TempDate), Null()) as [Calendário Mestre.Trimestre],
WeekDay(TempDate) as [Calendário Mestre.Dia da Semana]
Resident TempCalendar
Order By TempDate asc;

Drop Table TempCalendar;

But recently I need to add a feature called Fiscal Year Calendar on structure above.

So I'd like a suggestion related:

• How can I start a Quarter based on specific Month? (eg. Q1 = MAR, APR, MAY)

[]'s

Andre Moreira

[afurtado]

Maybe this can help you

Fiscal Year

[Anonymous]

Hi Alessandro,

Let me try it!

[]'s

Andre Moreira

[Anonymous]

Alessandro,

So based on example from Henric let me to adapt the example to my code:

// TODO - Implement the Fiscal Year feature
Set vFirstMonth = 3;


// TODO - Implement the Fiscal Year feature
,
Year(TempDate) + If(Month(TempDate) >= $(vFirstMonth), 1, 0) as [Calendário Mestre.Ano Fiscal],
Mod(Month(TempDate) - $(vFirstMonth), 12) + 1 as [Calendário Mestre.Mês Fiscal]

Until now trying to do the rule above works.

[]'s

Andre Moreira

[Anonymous]

Alessandro,

The example not worked.

Additionally I tried to write manually the Quarters Map:

QuartersMap:
Mapping Load * Inline [
Month, Quarter
1, Q4
2, Q4
3, Q1
4, Q1
5, Q1
6, Q2
7, Q2
8, Q2
9, Q3
10, Q3
11, Q3
12, Q4
];

But for Q4 not worked very well.

Assuming the example that Q4 means a range between dec-2017, jan-2018 and feb-2018.

My data model understood that Q4 is the range between dec-2017, jan-2017 and feb-2017.

Do you have another suggestion?

[]'s

Andre Moreira

[Anonymous]

All,

Someone has another suggestion?

[]'s

Andre Moreira

[Anil_Babu_Samineni]

How is your environment variables looks like? If you change Month from 4 - 3. It will start from 3 that means March as you needed

[sathish_kumar]

Hello Andre,

Did you arrive at any solution of having calendar year and fiscal year in the same script?

can you share the output in this forum?

Thanks