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: 
qlikhans
Creator
Creator

crosstable heading month to period

Hi,

please see attached files

I have a cross table:

  

Rubriek2_NameYear010203040506070809101112
Revenues2015-10.000-15.000-20.000-25.000-30.000-35.000-40.000-45.000-50.000-55.000-60.000-65.000
Revenues2016-70.000-80.000-90.000-100.000-110.000-120.000-130.000-140.000-150.000-160.000-170.000-180.000

and a regular table

  

Rubriek2_NameDateRealised Amount
Revenues1-1-2015-5000
Revenues1-2-2015-12000
Revenues1-3-2015-8000
Revenues1-4-2015-15000
Revenues1-5-2015-16500
Revenues1-6-2015-19100
Revenues1-7-2015-21700
Revenues1-8-2015-24300
Revenues1-9-2015-26900
Revenues1-10-2015-29500

the needed output is:

 

PeriodPlanActual
2015-01-10.000-5000
2015-02-15.000-12000
2015-03-20.000-8000
2015-04-25.000-15000

Period is setup as Year(Date)&'-'&Month(Date) as Period

How to convert the period numbers in the cross table to Period?

1 Solution

Accepted Solutions
sunny_talwar

MonthName function returns MMM YYYY format (in your case MMM is set as 01, 02, 03, .....)

MonthName(Date#(Num#(MonthNo) &'-' & Year, 'MMM-YYYY')) as Period,

The above (MMM-YYYY) is used for Date#() function which is used to help QlikView for Interpretation of a particular date style. If you want MMM-YYYY for some reason, you can try this:

Date(MonthStart(Date#(Num#(MonthNo) &'-' & Year, 'MMM-YYYY')), 'MMM-YYYY') as Period,

Read about Date here:

Get the Dates Right

Why don’t my dates work?

View solution in original post

5 Replies
sunny_talwar

May be this:

Table:

CrossTable(MonthNo, Amount, 2)

LOAD Rubriek2_Name,

    Year,

    [01],

    [02],

    [03],

    [04],

    [05],

    [06],

    [07],

    [08],

    [09],

    [10],

    [11],

    [12]

FROM

[example.xls]

(biff, embedded labels, table is Plan$);

FinalTable:

LOAD Rubriek2_Name,

  MonthName(Date#(Num#(MonthNo) &'-' & Year, 'MMM-YYYY')) as Period,

  Amount

Resident Table;

DROP Table Table;

LOAD Rubriek2_Name,

    Date,

    MonthName(Date) as Period,

    [Realised Amount]

FROM

[example.xls]

(biff, embedded labels, table is Actual$);

qlikhans
Creator
Creator
Author

HI Sunny,

it is close I think. The output of the Period is for example '01 2015', where from the coding I would expect '01-2015'.

Any idea?

krgrds

sunny_talwar

MonthName function returns MMM YYYY format (in your case MMM is set as 01, 02, 03, .....)

MonthName(Date#(Num#(MonthNo) &'-' & Year, 'MMM-YYYY')) as Period,

The above (MMM-YYYY) is used for Date#() function which is used to help QlikView for Interpretation of a particular date style. If you want MMM-YYYY for some reason, you can try this:

Date(MonthStart(Date#(Num#(MonthNo) &'-' & Year, 'MMM-YYYY')), 'MMM-YYYY') as Period,

Read about Date here:

Get the Dates Right

Why don’t my dates work?

sunny_talwar

This looks better now?

Capture.PNG

qlikhans
Creator
Creator
Author

Hi Sunny,

that did the trick. The links to understanding dates are also what I needed

thanks for the help

krgrds

Hans