4 Replies Latest reply: Jan 29, 2013 9:53 AM by Mark Sheraton

# Calendar Hours Problem

Hi Guys,

I have a FactTable with following HourlyData and it varies for CalendarHrs [01 to 24] and FiscalHrs [06 to 05]:

In my Qlikview App, I have named the Hours according to FiscalHrs in my Facttable.

CalenderHrsFiscalHrs
0106
0207
0308
0409
0510
0611
0712
0813
0914
1015
1116
1217
1318
1419
1520
1621
1722
1823
1924
2001
2102
2203
2304
2405

I have a report based on the FiscalHrs and when I select NETZT = NW, I get 14 to 24 Hrs and this is correct. When I select NETZT = SW, I get 06 to 14 and 01 to 05. Customer don't want to see 14,01,02,03,04,05 as 14 as already been in NETZT = NW and 01 to 05 belong to another day.

Can anyone help how to solve it. Got confused. Seems simple but It is not... Attached is the example.

Thanks
Sravan

• ###### Re: Calendar Hours Problem

hi

To explain the problem in detail:

I have named my facttable with Hrs as follows:

Facttable_temp:
Crosstable (Stunde  [Hour], Wert [Value] )

ID1,

ID2,

Date,

TW1 as '06',

TW2 as '07',

TW3 as '08',

TW4 as '09',

TW5 as 10,

TW6 as 11,

TW7 as 12,

TW8 as 13,

TW9 as 14,

TW10 as 15,

TW11 as 16,

TW12 as 17,

TW13 as 18,

TW14 as 19,

TW15 as 20,

TW16 as 21,

TW17 as 22,

TW18 as 23,

TW19 as 24,

TW20 as '01',

TW21 as '02',

TW22 as '03',

TW23 as '04',

TW24 as '05'

From....

;

FactTable:

ID1 & Date & Stunde as Key1...,

ID2 & Date & Stunde as Key2

Date & Stunde as Date,

Stunde,

Day

Resident Facttable_temp;

Drop table Facttable_temp;

and I have the NETZT.NETZT = SW with NETZT.GUELTIG_VON_VARIABLE_MS [NETZT.VALIDFROM] as 2010111818 [YYYYMMDDhh] to NETZT.GUELTIG_BIS_VARIABLE_MS [NETZT.VALIDTO] as 2011050914 [YYYYMMDDhh]

The Date is connected to Calendar [Year, Month, Day etc..] which is in Format YYYYMMDDhh.

There is a intervalmatch between Date and NETZT.GUELTIG_VON_VARIABLE_MS and NETZT.GUELTIG_BIS_VARIABLE_MS and I get the values in calendar for the above example when I select Year = 2011

Month = May [05], Day= 09, NETZT.NETZT = SW as follows in calendar

2011050901

2011050902

2011050903

2011050904

2011050905

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

but actually according to my need, I need only values from and to as my FiscalHrs start from 06 and ends at 05

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

How can I make my Calendar think that the date counts like this:

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

2011050915

2011050916

2011050917

2011050918

2011050919

2011050920

2011050921

2011050922

2011050923

2011050924

2011051001

2011051002

2011051003

2011051004

2011051005

Hope someone can help me. Its a lot tricky to do I guess...

Sravan

• ###### Re: Calendar Hours Problem

I generated calendar like this but it seems to be wrong

LET qDateMin = Num(MakeDate(2010,10,01)) -1;

LET qDateMax = Num(Today());

FOR i = 1 to 24        // Hour 0 to 23

TempCalendar:

LOAD date(\$(qDateMin) + IterNo()) as TempDate,

num(\$(i),'00') as Hour

AUTOGENERATE 1

WHILE \$(qDateMin) + IterNo() <= \$(qDateMax)

;

NEXT i

Calendar:

Date(TempDate,'YYYYMMDD')& Hour as GASTAG,

TempDate,

Hour,

If(num(Month(TempDate)) >= '10', 'GWJ ' & Right(Year(TempDate),2)  &'/'& Right((Year(TempDate)+1),2) , 'GWJ ' & Right(Year(TempDate)-1,2)  &'/'& Right((Year(TempDate)),2)) as FISCALYEAR,

num(Day(TempDate),'00')                     as Day,         //CalendarDayOfMonth,

Month(TempDate)                 as Month,         //CalendarMonthName,

//    'Q' & Ceil(Month(TempDate)/3)     as Quartal,

Year(TempDate)                     as KY, //CalendarYear,

//###Calendar Date Names###

WeekName(TempDate)                 as Kalenderwoche //CalendarWeekNumberAndYear,

resident

TempCalendar

//order by

//    TempDate ASC

;

DROP TABLE TempCalendar;

• ###### Re: Calendar Hours Problem

can't believe no answer...Is it tough or I am not clear?

• ###### Re: Calendar Hours Problem

I don't understand what you are trying to do.

The qvw. you uploaded is hard to understand as all the field names are in German and I don't speak / read German.  I'm guessing that put a lot of people off helping as this is an English speaking forum.  The subsequent replies just confused me further.

Maybe you could start again with an example of the data you have.  Then what translations you want to occur and how you expect the data to look at the end?

I still might not be able to help but I'm sure someone could...