5 Replies Latest reply: Jun 9, 2016 2:42 PM by Hans kleijnen

# crosstable heading month to period

Hi,

I have a cross table:

 Rubriek2_Name Year 1 2 3 4 5 6 7 8 9 10 11 12 Revenues 2015 -10 -15 -20 -25 -30 -35 -40 -45 -50 -55 -60 -65 Revenues 2016 -70 -80 -90 -100 -110 -120 -130 -140 -150 -160 -170 -180

and a regular table

 Rubriek2_Name Date Realised Amount Revenues 1-1-2015 -5000 Revenues 1-2-2015 -12000 Revenues 1-3-2015 -8000 Revenues 1-4-2015 -15000 Revenues 1-5-2015 -16500 Revenues 1-6-2015 -19100 Revenues 1-7-2015 -21700 Revenues 1-8-2015 -24300 Revenues 1-9-2015 -26900 Revenues 1-10-2015 -29500

the needed output is:

 Period Plan Actual 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?

• ###### Re: crosstable heading month to period

May be this:

Table:

CrossTable(MonthNo, Amount, 2)

Year,

[01],

[02],

[03],

[04],

[05],

[06],

[07],

[08],

[09],

[10],

[11],

[12]

FROM

[example.xls]

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

FinalTable:

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

Amount

Resident Table;

DROP Table Table;

Date,

MonthName(Date) as Period,

[Realised Amount]

FROM

[example.xls]

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

• ###### 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

• ###### 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,

Get the Dates Right

Why don’t my dates work?

• ###### Re: crosstable heading month to period

This looks better now?

• ###### 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