Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

qlikhans
Not applicable

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?

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: crosstable heading month to period

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?

5 Replies
sunny_talwar
Not applicable

Re: crosstable heading month to period

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
Not applicable

Re: crosstable heading month to period

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
Not applicable

Re: crosstable heading month to period

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
Not applicable

Re: crosstable heading month to period

This looks better now?

Capture.PNG

qlikhans
Not applicable

Re: crosstable heading month to period

Hi Sunny,

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

thanks for the help

krgrds

Hans