topic Re: Creating Quarters and MTD with "CrossTabled" data in QlikView

Creating Quarters and MTD with "CrossTabled" data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Creating Quarters and MTD with "CrossTabled" data

sunny_talwar — Tue, 03 Apr 2018 15:40:25 GMT

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

Re: Creating Quarters and MTD with "CrossTabled" data

Anonymous — Tue, 03 Apr 2018 15:58:35 GMT

Yes, only Month information.

Re: Creating Quarters and MTD with "CrossTabled" data

sunny_talwar — Tue, 03 Apr 2018 16:07:42 GMT

What is MTD in that case?

Re: Creating Quarters and MTD with "CrossTabled" data

Anonymous — Tue, 03 Apr 2018 16:10:35 GMT

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.

Re: Creating Quarters and MTD with "CrossTabled" data

suepenick — Tue, 03 Apr 2018 16:23:42 GMT

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

sunny_talwar — Tue, 03 Apr 2018 16:52:28 GMT

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

MarcoWedel — Tue, 03 Apr 2018 20:46:56 GMT

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

Re: Creating Quarters and MTD with "CrossTabled" data

Anonymous — Wed, 04 Apr 2018 11:34:47 GMT

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?