Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
letileti
New Contributor II

Mini challenge - how to create common month dimension for 2 measures

Hi all,

someone who can help me achieving this result?

many Thanks for looking into it

Result:

Month;amount (launch);amount(target)

Jan;120;50

feb;o;200

i would like to have the breakdown at lower levels (project) too, but ignored it to simplify

Original data:

Project;month(launch);amount(launch);month(target);amount(target)

1;jan;100;feb;200

2;jan;20;jan;50

 

Labels (1)
2 Solutions

Accepted Solutions

Re: Mini challenge - how to create common month dimension for 2 measures

What you need here is to use a Canonical Date here

Re: Mini challenge - how to create common month dimension for 2 measures

Simplified version of the script for the simplified data you provided

Table:
LOAD Project,
	 Month(Date#(Launch_Month, 'MMM')) as Launch_Month,
	 Launch_Amount,
	 Month(Date#(Target_Month, 'MMM')) as Target_Month,
	 Target_Amount;
LOAD * INLINE [
    Project, Launch_Month, Launch_Amount, Target_Month, Target_Amount
    1, Jan, 100, Feb, 200
    2, Jan, 20, Jan, 50
];

DateBridge:
LOAD Project,
	 Launch_Month as Month,
	 'Launch' as DateType
Resident Table;
LOAD Project,
	 Target_Month as Month,
	 'Target' as DateType
Resident Table;

image.png

3 Replies

Re: Mini challenge - how to create common month dimension for 2 measures

What you need here is to use a Canonical Date here

Re: Mini challenge - how to create common month dimension for 2 measures

Simplified version of the script for the simplified data you provided

Table:
LOAD Project,
	 Month(Date#(Launch_Month, 'MMM')) as Launch_Month,
	 Launch_Amount,
	 Month(Date#(Target_Month, 'MMM')) as Target_Month,
	 Target_Amount;
LOAD * INLINE [
    Project, Launch_Month, Launch_Amount, Target_Month, Target_Amount
    1, Jan, 100, Feb, 200
    2, Jan, 20, Jan, 50
];

DateBridge:
LOAD Project,
	 Launch_Month as Month,
	 'Launch' as DateType
Resident Table;
LOAD Project,
	 Target_Month as Month,
	 'Target' as DateType
Resident Table;

image.png

letileti
New Contributor II

Re: Mini challenge - how to create common month dimension for 2 measures

Thank you so Much! I read the post you shared and that is exactly what I need! Going to try it out immediately
Community Browser