Creating Quarters and MTD with "CrossTabled" data

[Anonymous]

Hello folks,

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

Family	MonthY	Tot
x	Jan	20
x	Feb	30
Y	Dec	55
z	Dec	10000

Best Regards,

Filippo

[sunny_talwar]

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

[Anonymous]

Yes, only Month information.

[sunny_talwar]

What is MTD in that case?

[Anonymous]

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]

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

Hi,

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

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]

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?