Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get dimensions of month and year from cross table data

Hi,

as per screen shot, I need the following to be displayed as Months and 2 lines representing the years.

The data for the YearMonth had been cross table

I could use excel to create a whole new column for Month and another for Year to do it but I think there should be a way in Qlikview.

Any help would be very much appreciated.

thank you

1 Solution

Accepted Solutions
sunny_talwar

Create a Year and Month field in resident load after your CrossTable Load and use Month as your first dimension and Year as your second dimension in the above chart.

View solution in original post

5 Replies
sunny_talwar

Create a Year and Month field in resident load after your CrossTable Load and use Month as your first dimension and Year as your second dimension in the above chart.

Not applicable
Author

With these example script you can get YEAR and MONTH

WITH 201401:

YEAR(MAKEDATE(LEFT('201401',4), RIGHT('201401',2)*1))

MONTH(MAKEDATE(LEFT('201401',4), RIGHT('201401',2)*1))

Regards,

Not applicable
Author

Hi,

How should I go about creating the Year and Month field please?

Thanks

Not applicable
Author

Hi,

I have managed with the direction of resident Load.

Is alot more complicated than I expected and also needed to define the Month and Year.

thank you for pointing me in the right direction.

///

FROM

(biff, embedded labels);

NoConcatenate

New:

LOAD

     ***

    Month(Date(Date#([When],'YYYYMM'))) as Month,

    Year(Date(Date#([When],'YYYYMM'))) as Year

    

Resident first;

DROP Table first;   

///

sunny_talwar

May be something like this:

FinalTable:

LOAD *,

          Year(YearMonth) as Year,

          Month(YearMonth) as Month;

LOAD Total_2014,

          Total_2015,

          Type,

          Line,

          [Trade Route],

          [Port Pair],

          Date(Date#(YearMonth, ‘YYYYMM’) , ‘YYYYMM’) as YearMonth,

          Boxes

Resident AboveCrossTableNameHere;