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

    Calendar Hours Problem

    Sravan Puppala

      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
          Sravan Puppala

          hi

           

          To explain the problem in detail:

          I have named my facttable with Hrs as follows:

           

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

          Load

                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:

           

          Load

          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...

           

          Thanks in Advance

          Sravan

            • Re: Calendar Hours Problem
              Sravan Puppala

              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: 

              load

               

                  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
                  Sravan Puppala

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

                    • Re: Calendar Hours Problem
                      Mark Sheraton

                      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...