Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Calendar -QTD

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

 

7 Replies
Highlighted
Specialist II
Specialist II

Any inputs pls??

Highlighted

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?

Highlighted
Specialist II
Specialist II

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

 

 

Highlighted

I would do something like this

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

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);
Highlighted
Specialist II
Specialist II

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
;

 

Highlighted
Digital Support
Digital Support

@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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.