Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

neale1970
New 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
OmarBenSalem
Esteemed Contributor

Re: Months to Quarters Sales - Cross Table

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

6 Replies
OmarBenSalem
Esteemed Contributor

Re: Months to Quarters Sales - Cross Table

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

neale1970
New Contributor III

Re: Months to Quarters Sales - Cross Table

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

OmarBenSalem
Esteemed Contributor

Re: Months to Quarters Sales - Cross Table

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;

neale1970
New Contributor III

Re: Months to Quarters Sales - Cross Table

Hi Omar,

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

Thanks very much for your help

Neale

OmarBenSalem
Esteemed Contributor

Re: Months to Quarters Sales - Cross Table

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

neale1970
New Contributor III

Re: Months to Quarters Sales - Cross Table

Have just done so. Many thanks Omar.

Community Browser