2 Replies Latest reply: May 2, 2017 12:08 PM by Alexander Grando RSS

    Creating Date Flag Columns or Rows

    Alexander Grando

      Hi everyone,

       

      I have a script at yearly granularity for the past 4 years that I would like to modify so that it also includes quarterly, monthly, weekly, and daily granularity for the past 4 years. Attached is a sample app with sample data. My goal is to create a line trend with a cyclical group dimension that cycles through these different grains. This method of scripting in date columns is the only thing I can think of for accomplishing this. Your help would be greatly appreciated. Thanks in advance!

        • Re: Creating Date Flag Columns or Rows
          Shiva Nagesh

          which date column you need to be in ranges of week, month and quarter??

           

          With this part of script

          Data:

          LOAD %PropertyKey,

               date(BegDateOfOperation) as BegDateOfOperation,

               date(EndDateOfOperation) as EndDateOfOperation

          FROM

          [C:\Users\agrando\Desktop\Alex's Stuff\Sample Data.xlsx]

          (ooxml, embedded labels, table is Sheet2);

           

           

          LEFT JOIN (Data)

          LOAD year(Today())-IterNo()+1 as YearGranularity

          AutoGenerate 1

          While IterNo() <=4;

           

           

          as you are not having YearGranularity field in Data table, its a cross join. You may need interval match with calendar and BegDateOfOperation,EndDateOfOperation.

          IntervalMatch

            • Re: Creating Date Flag Columns or Rows
              Alexander Grando

              Hi Shiva,

               

              Thank you for your response. I'm still very new to QlikView and not sure what you mean. I'm trying to trend the count of active %PropertyKey on any given granularity specified by the user. The BegDateOfOperation and EndDateOfOperation columns are used to calculate whether the property was active during the time frame that the user specifies. Please let me know if you have any more questions or comments.