6 Replies Latest reply: Nov 26, 2017 5:47 PM by Neale Forth RSS

    Months to Quarters Sales - Cross Table

    Neale Forth

      Hi QLIK Community,

       

      I need to be able to show graphical data, in quarterly time buckets. The data I am getting is in monthly buckets and is in tabular format so not ideal to import into QLIK. So I'm thinking best solution would be to convert monthly to quarterly data using a cross table - example of what I mean below :

      M2Q.PNG

      I have tried to do this but not really and the cross table works but the summing up into quarters doesn't. Any advise on if this is the right/best approach or how to do this would be most gratefully received !

       

      Thanks

       

      Neale

        • Re: Months to Quarters Sales - Cross Table
          omar bensalem

          tab:

          Crosstable (Month, Sales) LOAD * INLINE [

          company,Jan-17, Feb-17, Mar-17 , Apr-17, May-17, Jun-17

          company1, 50, 50, 50, 50, 50, 50

          company2, 150,150,150,150,150,150

          ];

           

           

          Quarters1:

          NoConcatenate

          load *,Year(DateField)&' Q'&Ceil(Month(DateField)/3) as Quarter;

          load company as CompanyName,

          date(Date#(Month,'MMM-YY'),'MMMM YYYY') as Month,

          date(Date#(Month,'MMM-YY')) as DateField,

          Sales ;

          Load * Resident tab;

           

           

          drop Fields Month, DateField;

          drop table tab;

           

           

          Quarters:

          NoConcatenate

          load CompanyName, sum(Sales) as Sales, Quarter Resident Quarters1

          Group By Quarter,CompanyName ;

           

           

          Drop Table Quarters1;

           

          result:

          Capture.PNG

            • Re: Months to Quarters Sales - Cross Table
              Neale Forth

              Hi Omar.

              Many Thanks ! I can follow your script but my QLIK "grammar" is not really up to scratch yet !

               

              I have tried to adapt this code to load the source data from Excel file - as opposed to an in-line table load , but without success - my script to load excel table is as follows, I get a "field company not found"....

               

              Can you advise what I am doing wrong ?

               

              Huge thanks !

               

              Neale

               

              Tab:

              Crosstable (Month,Sales,1)

               

              LOAD

                  "Company Name",

                  "Jan-17",

                  "Feb-17",

                  "Mar-17",

                  "Apr-17",

                  "May-17",

                  "Jun-17",

                  "Jul-17",

                  "Aug-17",

                  "Sep-17",

                  "Oct-17",

                  "Nov-17",

                  "Dec-17",

                  "Jan-18",

                  "Feb-18",

                  "Mar-18",

                  "Apr-18",

                  "May-18",

                  "Jun-18",

                  "Jul-18",

                  "Aug-18",

                  "Sep-18",

                  "Oct-18",

                  "Nov-18",

                  "Dec-18"

              FROM [lib://AttachedFiles/QuarterMonth.xlsx]

              (ooxml, embedded labels, table is Data);

                • Re: Months to Quarters Sales - Cross Table
                  omar bensalem

                  Qlik is case sensitive; maybe because ur table is Tab not tab and then u called as tab;

                  try as follow:

                   

                  Tab:

                  Crosstable (Month,Sales,1)

                   

                  LOAD

                      "Company Name",

                      "Jan-17",

                      "Feb-17",

                      "Mar-17",

                      "Apr-17",

                      "May-17",

                      "Jun-17",

                      "Jul-17",

                      "Aug-17",

                      "Sep-17",

                      "Oct-17",

                      "Nov-17",

                      "Dec-17",

                      "Jan-18",

                      "Feb-18",

                      "Mar-18",

                      "Apr-18",

                      "May-18",

                      "Jun-18",

                      "Jul-18",

                      "Aug-18",

                      "Sep-18",

                      "Oct-18",

                      "Nov-18",

                      "Dec-18"

                  FROM [lib://AttachedFiles/QuarterMonth.xlsx]

                  (ooxml, embedded labels, table is Data);

                   

                  Quarters1:

                  NoConcatenate

                  load *,Year(DateField)&' Q'&Ceil(Month(DateField)/3) as Quarter;

                  load "Company Name",

                  date(Date#(Month,'MMM-YY'),'MMMM YYYY') as Month,

                  date(Date#(Month,'MMM-YY')) as DateField,

                  Sales ;

                  Load * Resident Tab;

                   

                   

                  drop Fields Month, DateField;

                  drop table Tab;

                   

                   

                  Quarters:

                  NoConcatenate

                  load "Company Name", sum(Sales) as Sales, Quarter Resident Quarters1

                  Group By Quarter, "Company Name";

                   

                   

                  Drop Table Quarters1;

              • Re: Months to Quarters Sales - Cross Table
                Neale Forth

                Hi Omar,

                 

                It didn't like the Company Name .....after a little re-jigging its all worked !

                 

                Thanks very much for your help

                 

                Neale