4 Replies Latest reply: Jun 9, 2016 4:37 AM by Shamit Shah RSS

    Sorting by week

    Shamit Shah

      Hi,

       

      I have 2 bar charts -

       

      1) Revenue by month (Fiscal Year July 2015 - June 2016)

      2) Revenue by week

       

      The  second bar chart by week does not align i.e. the week numbers (by calendar month) do not align to the the Fiscal year July 2015 to June 2016.

       

      Is there any way to sort the week numbers so that they align to the Fiscal months?

       

      Thanks

      Shamit

        • Re: Sorting by week
          Stefan Wühl

          I would suggest that you create a master calendar with fiscal year, month, week etc. fields.

           

          See for example

          https://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year#comment-18082

          The Master Calendar

          Data Types in QlikView

           

          This new week field should then be sorted numeric to align with your fiscal year.

            • Re: Sorting by week
              Shamit Shah

              Hi swuehl,

               

              I have the following in the main script in the SALES table:

               

              Date([SAL_InvoiceDate] + MakeDate(2000,1,1)) as SaleDate

               

              I tried to follow step 3 in the example The Master Calendar above and ended up with the following to generate the dates:

               

              Load Date([SAL_InvoiceDate] + MakeDate(2000,1,1)) as CalendarSaleDate;

              Load Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as CalendarSaleMonth;

              Load Week(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as CalendarSaleWeek;

              Load Date(Min([SAL_InvoiceDate] + MakeDate(2000,1,1))+iterno()) as  CalendarSaleDate While iterno() <= Max(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) - Min(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) ;

              Load Min(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))-1 as MinDate, Max(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as MaxDate resident SALES;

               

              When I reload , I am getting the following error message:

               

              Any idea where I am going wrong?

               

              Thanks

              Shamit