crosstable heading month to period

[qlikhans]

Hi,

please see attached files

I have a cross table:

Rubriek2_Name	Year	01	02	03	04	05	06	07	08	09	10	11	12
Revenues	2015	-10.000	-15.000	-20.000	-25.000	-30.000	-35.000	-40.000	-45.000	-50.000	-55.000	-60.000	-65.000
Revenues	2016	-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_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?

[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]

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 · Accepted solution]

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?

[qlikhans]

Hi Sunny,

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

thanks for the help

krgrds

Hans