Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

8 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

Yes, only Month information.

sunny_talwar

What is MTD in that case?

Anonymous
Not applicable
Author

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
Creator
Creator

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.

sunny_talwar

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

MarcoWedel

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

Anonymous
Not applicable
Author

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?