Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
filippo_cardoso
New Contributor III

Creating Quarters and MTD with "CrossTabled" data

Hello folks,

How can I creat Quarters (Q1 to Q4) and MTD parameters using my table in that way?

   

FamilyMonthYTot
xJan20
xFeb30
YDec55
zDec10000

Best Regards,

Filippo

Tags (2)
1 Solution

Accepted Solutions

Re: Creating Quarters and MTD with "CrossTabled" data

Hi,

one solution to load the months as dual values and derive a quarter field might be:

QlikCommunity_Thread_296878_Pic1.JPG

table1:

LOAD *,

    Dual('Q'&Ceil(MonthY/3),Ceil(MonthY/3)) as Quarter;

LOAD Family,

    Month(Date#(MonthY,'MMM')) as MonthY,

    Tot

FROM [https://community.qlik.com/thread/296878] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

8 Replies

Re: Creating Quarters and MTD with "CrossTabled" data

Do you only have Month information? Not Day or Year information is available?

filippo_cardoso
New Contributor III

Re: Creating Quarters and MTD with "CrossTabled" data

Yes, only Month information.

Re: Creating Quarters and MTD with "CrossTabled" data

What is MTD in that case?

filippo_cardoso
New Contributor III

Re: Creating Quarters and MTD with "CrossTabled" data

Month To Date.

For example: We are in April, So MTD of today is jan,feb,Mar

Other example: If we were in October, MTD would be from jan to Sep.

suepenick
Contributor

Re: Creating Quarters and MTD with "CrossTabled" data

DefiningQuarters:

Left Join (MyFileOfData)

Load * INLINE

[MonthY, Quarter

Jan,Q1

Feb,Q1

Mar,Q1

Apr,Q2

May,Q2

Jun,Q2

and so forth...

];

then

in your charts sum the Tot to get a Month to date if you put MonthY as a dimension in the chart

also if you use the Quarter, you will have a sum of the quarter.

Re: Creating Quarters and MTD with "CrossTabled" data

You mean to say Year to date

MTD for April is 04/01/2018 to 04/03/2018

YTD for April is 01/01/2018 to 04/03/2018

Re: Creating Quarters and MTD with "CrossTabled" data

Hi,

one solution to load the months as dual values and derive a quarter field might be:

QlikCommunity_Thread_296878_Pic1.JPG

table1:

LOAD *,

    Dual('Q'&Ceil(MonthY/3),Ceil(MonthY/3)) as Quarter;

LOAD Family,

    Month(Date#(MonthY,'MMM')) as MonthY,

    Tot

FROM [https://community.qlik.com/thread/296878] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

filippo_cardoso
New Contributor III

Re: Creating Quarters and MTD with "CrossTabled" data

Yeah, My mistake.

But, in my company, Ytd goes from jan to the last month that already has passed.

Do you know how to do that?