Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Months to Quarters Sales - Cross Table

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

1 Solution

Accepted Solutions
Partner
Partner

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

View solution in original post

6 Replies
Partner
Partner

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

View solution in original post

Contributor III
Contributor III

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);

Partner
Partner

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;

Contributor III
Contributor III

Hi Omar,

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

Thanks very much for your help

Neale

Partner
Partner

Glad to help. If your question has been answered, please close the thread by marking the correct answer as correct.

Contributor III
Contributor III

Have just done so. Many thanks Omar.