2 Replies Latest reply: Aug 10, 2017 7:58 AM by Sunny Talwar RSS

    Quarter from months in Crosstable?

    Peter Viberg

      Hi!

       

      I have some sales data with Sales per month (only month name columns) which i would like to load into my app.

       

      The data looks like this

       

      YearCustomerProductSum1Sum2Sum3Sum4....
      2017MrHeyFanta299110050
      2016MrHeyFanta30005006000
      2016MrsBuyerPlats55533308000
      2015Hula Hula INCSunscreen54565445555

       

      Where the "SumX" columns represent sales in Jan, Feb, Mar etc. I could ofcourse rename the columns, but the year has to be in the year column.

       

      And the LOAD script looks like this:

       

      Sales:

      LOAD

          Year,

          Customer,

          Product,

         num(Sum1, '# ##0','.',' ') as Jan,

         num(Sum2, '# ##0','.',' ') as Feb,

          num(Sum3, '# ##0','.',' ') as Mar,

          num(Sum4, '# ##0','.',' ') as Apr,

          num(Sum5, '# ##0','.',' ') as May,

          num(Sum6, '# ##0','.',' ') as June,

          num(Sum7, '# ##0','.',' ') as July,

          num(Sum8, '# ##0','.',' ') as Aug,

          num(Sum9, '# ##0','.',' ') as Sep,

          num(Sum10, '# ##0','.',' ') as Oct,

          num(Sum11, '# ##0','.',' ') as Nov,

          num(Sum12, '# ##0','.',' ') as Dec

         

      FROM [lib://QlikSenseDB/datatestkolumner.xlsx]

      (ooxml, embedded labels, table is Blad1);

       

      Sales_T1:

      CROSSTABLE ( Month, Sum, 3 )

      LOAD

      *

      RESIDENT

      Sales;

       

      Sales_pivot:

      LOAD

          Year,

          Customer,

          Product,

          Month,

          Sum

         

      RESIDENT

      Sales_T1

      WHERE

      isnum( Sum );

       

       

       

      What I would like is to add Quarter without adding more columns in the data source file. How do I do this?

       

      And also, is it possible to improve the " num(Sum1, '# ##0','.',' ') as Jan," part of the LOAD statement (it works but doesn't look optimal)?


      Thanks a lot!

        • Re: Quarter from months in Crosstable?
          Dan Sullivan

          You could set the "SumX" columns as a YearMonth combination then bring in a master calendar that joins against that column for additional calendar attributes.

          • Re: Quarter from months in Crosstable?
            Sunny Talwar

            May be try this

             

            Table:

            CrossTable(Month, Sum, 3)

            LOAD * INLINE [

                Year, Customer, Product, Sum1, Sum2, Sum3, Sum4

                2017, MrHey, Fanta, 299, 110, 0, 50

                2016, MrHey, Fanta, 3000, 500, 600, 0

                2016, MrsBuyer, Plats, 555, 333, 0, 8000

                2015, Hula Hula INC, Sunscreen, 5456, 54, 4, 5555

            ];

             

            FinalTable:

            LOAD *,

            Date(MakeDate(Year, Month), 'MMM-YYYY') as MonthYear,

            'Q' & Ceil(Month/3) as Quarter;

            LOAD Year,

            Customer,

            Product,

            Month(Date#(KeepChar(Month, '0123456789'), 'M')) as Month,

            Sum

            Resident Table;

             

            DROP Table Table;