topic Re: QlikView - Fiscal Year Calendar in Brasil

QlikView - Fiscal Year Calendar

Anonymous — Fri, 03 Nov 2017 16:48:18 GMT

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

Re: QlikView - Fiscal Year Calendar

afurtado — Sat, 04 Nov 2017 17:11:29 GMT

Maybe this can help you

Fiscal Year

Re: QlikView - Fiscal Year Calendar

Anonymous — Mon, 06 Nov 2017 12:11:37 GMT

Hi Alessandro,

Let me try it!

[]'s

Andre Moreira

Re: QlikView - Fiscal Year Calendar

Anonymous — Mon, 06 Nov 2017 15:55:13 GMT

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

Re: QlikView - Fiscal Year Calendar

Anonymous — Mon, 06 Nov 2017 16:27:57 GMT

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

Re: QlikView - Fiscal Year Calendar

Anonymous — Tue, 21 Nov 2017 17:50:34 GMT

All,

Someone has another suggestion?

[]'s

Andre Moreira

Re: QlikView - Fiscal Year Calendar

Anil_Babu_Samineni — Wed, 22 Nov 2017 06:03:18 GMT

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

Re: QlikView - Fiscal Year Calendar

sathish_kumar — Fri, 19 Oct 2018 07:50:37 GMT

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