Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

peterviberg
New Contributor

Quarter from months in Crosstable?

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!

Tags (2)
1 Solution

Accepted Solutions

Re: Quarter from months in Crosstable?

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;

2 Replies
dan_sullivan
Contributor II

Re: Quarter from months in Crosstable?

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?

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;

Community Browser