topic Re: Calendar -QTD in QlikView

Calendar -QTD

krishna20 — Sat, 16 Nov 2024 18:10:24 GMT

Hi Folks,

I need to include the QTD in the below calendar under the Period flag. Could anyone advise me how to implement this?

As I have 5yrs of data , QTD should reflect all these years. If it is not possible in the below script, advise any other logics in the master calendar.

Note: QTD,MTD,YTD should be in a single field.

Month:
LOAD * INLINE [
Month, Quarter, MonthDesc
1, Q1, Jan
2, Q1, Feb
3, Q1, Mar
4, Q2, Apr
5, Q2, May
6, Q2, Jun
7, Q3, Jul
8, Q3, Aug
9, Q3, Sep
10, Q4, Oct
11, Q4, Nov
12, Q4, Dec
];

Calendar:
LOAD Distinct Month, 'MTD' as Period Resident Fact;

Left Join (Calendar)

Load
Month, Quarter , Date(Date#(MonthDesc,'MMM'),'MMM') as MonthDesc
resident Month;

Concatenate

LOAD

IterNo() as Month,
MonthDesc,
'YTD' as Period,
Quarter
resident Calendar
While IterNo() <= Month;
Drop Table Month;

@sunny_talwar , @jagan , @Gysbert_Wassenaar
Thanks

Krishna

Re: Calendar -QTD

krishna20 — Mon, 31 Aug 2020 06:55:27 GMT

Any inputs pls??

Re: Calendar -QTD

sunny_talwar — Mon, 31 Aug 2020 12:24:36 GMT

You want QTD flag for each of the last 5 years? meaning, right now the flag will show 1 for all months of Jul, Aug, and Sep? and rest of the months will be 0?

Re: Calendar -QTD

krishna20 — Mon, 31 Aug 2020 14:33:32 GMT

Yes, right sunny. The flag should show 1 for all months of Jul, Aug, and Sep and rest will be 0.

Re: Calendar -QTD

sunny_talwar — Mon, 31 Aug 2020 15:03:07 GMT

I would do something like this

If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 1, 0) as InQuarterFlag

Re: Calendar -QTD

sunny_talwar — Mon, 31 Aug 2020 15:12:18 GMT

Here is a sample script

Table: LOAD *, If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 1, 0) as InQuarterFlag, Month(TempDate) as MonthDesc, Num(Month(TempDate)) as Month, 'Q' & Ceil(Month(TempDate)) as Quarter; LOAD Date(MakeDate(Year(Today())-5) + IterNo() - 1) as TempDate AutoGenerate 1 While MakeDate(Year(Today())-5) + IterNo() - 1 < MakeDate(Year(Today())+1);

Re: Calendar -QTD

krishna20 — Mon, 31 Aug 2020 16:16:27 GMT

Hi Bhai,

Thanks for lighting up on the issue.

I have modified my script with your logic given. But not getting expected output.

I need MTD, QTD, YTD all together in a single field.

QTD is mapping and now YTD and MTD is not mapping to the fact data.

Calendar:
LOAD *,
If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 'QTD') as PeriodScope,
Month(TempDate) as MonthDesc,
Num(Month(TempDate)) as Month,
'Q' & Ceil(Month(TempDate)/3) as Quarter;
LOAD Date(MakeDate(Year(Today())-5) + IterNo() - 1) as TempDate
AutoGenerate 1
While MakeDate(Year(Today())-5) + IterNo() - 1 < MakeDate(Year(Today())+1);

Concatenate
Load
If(Day(TempDate) <= Day(Today()) and Month(TempDate) = Month(Today()), 'MTD')as PeriodScope

Resident Calendar;

Concatenate
LOAD
IterNo() as Month,

'YTD' as PeriodScope

Resident Calendar
While IterNo() <= Month
;

Re: Calendar -QTD

Brett_Bleess — Tue, 15 Sep 2020 17:08:38 GMT

@sunny_talwar Further information provided on requirement, any chance you can have another look and provide adjusted code to handle, this one is over my head? I have to say I am a bit confused on the requirement, hopefully it makes sense to you though.

Regards,
Brett