Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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
Accepted Solutions
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:
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:
Hi Omar,
It didn't like the Company Name .....after a little re-jigging its all worked !
Thanks very much for your help 
Neale