Announcements
cancel
Showing results 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?

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

Best Regards,

Filippo

1 Solution

Accepted Solutions
MVP

Hi,

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

```table1:
Dual('Q'&Ceil(MonthY/3),Ceil(MonthY/3)) as Quarter;
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
MVP

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

Anonymous
Not applicable
Author

Yes, only Month information.

MVP

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.

Creator

DefiningQuarters:

Left Join (MyFileOfData)

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

MVP

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

MVP

Hi,

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

```table1:
Dual('Q'&Ceil(MonthY/3),Ceil(MonthY/3)) as Quarter;
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?

Community Browser