QTD calculation in Script

[krishna20]

Hi Folks,

I need to calculate QTD in script and using the below logic , but not working as expected. It gives me only latest QTD , I need logic for whole year.

If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()) and Ceil(Month(TempDate)/3) = Ceil(Month(Today())/3)) as QTD

Expected O/P:

Jan	Q1
Feb	Q1
Mar	Q1
Apr	Q2
May	Q2
Jun	Q2
Jul	Q3
Aug	Q3
Sep	Q3
Oct	Q4
Nov	Q4
Dec	Q4

Could anyone help me to get the correct logic.

Reagrds

Krishna

[krishna20]

Any inputs??

[edwin]

assuming you have a range of Dates the user selects and you have a calendar, you can build a bridge that associates each date in the calendar to the respective QTD record in your fact table.  so the association will be like the following (sample for 02/05/2021):

02/05/2021 -> 02/05/2021
02/05/2021 -> 02/04/2021
...
02/05/2021 -> 01/01/2021

there are many ways to do this, one way would be to create a temporary table which is a cartesian join between your factdate and calendar, then filter out the dates that fall outside of the QTD condition:

noconcatenate Bridge: load FactDate, Date, 'QTD' as DaterType resident tmpTable where quarterstart(FactDate)=quarterstart(Date) and FactDate<=Date;

of course you need to link the dailies:

concatenate (Bridge) load FactDate, FactDate as Date, 'DAILY' as DateType residnet tmpTable;

to use this, if you want to select only the Facts on the selected Date, add in your expression set analysis

{< ..., DateType={'DAILY'}, ...>}

to get all the QTD rows (when user selects a Date in the Calendar, the following will link it to all the FactDates that is QTD):

{< ..., DateType={'QTD'}, ...>}

hope that helps

[edwin]

as add on, if you want it on month granularity, just select the MONTH in the calendar and still use DateType={'QTD'}

you can then expand this solution to MTD, YTD, PastQTD, PastMTD, PastYTD

[krishna20]

Hi Edwin,

Thanks for sharing your thoughts and it's valuable. Actually I am not having date field in my fact table, using month field I have created master calendar below is the one. 

To find exact QTD for all the months instead of today() what logic can be used to get this done?

Sub Calendar
Calendar:
LOAD Month Resident Fact;

Temp:
Load

min(makedate(2016,1,1)) as minDate,

max(makedate(2020,12,31)) as maxDate

Resident Calendar;

DROP Table Calendar;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

YTDCalendar:
Load Distinct

Month(TempDate) As Month,
Date(Date#(Month(TempDate),'MMM'),'MMM') as MonthDesc,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) as Quarter,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) &'_'& Date(Date#(Month(TempDate),'MMM'),'MMM') as QtrMonth,

'ITM' as PeriodScope

Resident TempCalendar;

Concatenate (YTDCalendar)
Load
Month(TempDate) As Month,

Date(Date#(Month(TempDate),'MMM'),'MMM') as MonthDesc,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) as Quarter,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) &'_'& Date(Date#(Month(TempDate),'MMM'),'MMM') as QtrMonth,
If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()) and Ceil(Month(TempDate)/3) = Ceil(Month(Today())/3), 'QTD') as PeriodScope

Resident TempCalendar;

Concatenate(YTDCalendar)
LOAD
IterNo() as Month,
MonthDesc,
QtrMonth,
Quarter,
'YTD' as PeriodScope
Resident YTDCalendar
While IterNo() <= Month
;

Drop Table TempCalendar;

END SUB

[Anil_Babu_Samineni]

Let's talk one year. Since you don't have date field in your calendar or fact. you need to do some manual (That make sense what your script already did)

But, Not entirely sure i understand your requirement on your logic? Can you brief little more on this part how you need expected and why you are trying in different like If--else.

---

@krishna20 wrote:

Expected O/P:

Jan	Q1
Feb	Q1
Mar	Q1
Apr	Q2
May	Q2
Jun	Q2
Jul	Q3
Aug	Q3
Sep	Q3
Oct	Q4
Nov	Q4
Dec	Q4

---

[edwin]

you are just changing the granularity of your data from DATE to month

from your script it looks like your date range is hard coded.  normally it should be in your Fact table (Month perhaps is a date field?)  you cant possibly have a Month field with just the Month name as Jan 2020 will then roll up with Jan 2021.  so it must be 1st of the month?

assuming Month is a date field

noconcatenate minMax: load date(min(Month)) as MinMonth, date(max(Month)) as MaxMonth resident Fact
//this gives you all the months between min and max months from your Fact
noconcatenate tmpBridge: load date(addmonths(MinMonth,iterno()-1)) as Month resident minMax
while  date(addmonths(MinMonth,iterno()-1)) <=MaxMonth;
//this is a cartesian join
inner join (tmpBridge) load Month as SelectMonth resident tmpBridge;
//this builds the relationship between your selected Month and QTD
noconcatenate Bridge: load Month, SelectMonth, 'Q'&ceil(month(SelectMonth)/3) as Quarter, 'QTD' as MonthType resident tmpBridge
where quarterstart(Month) = quarterstart(SelectMonth);
drop table tmpBridge;

to get the QTD let user select SelectMonth field, then in your set analysis use DateType={'QTD'}  it will give you all the months for same quarter

[edwin]

check this out

[krishna20]

Hi ,

@Anil_Babu_Samineni - I have given the requirement details and explanation what is using for what. Please let me know if you need any details further.

@edwin - Thanks a lot for your suggestions, I am looking for "QTD" not for Quarter calculation. We already have Quarter in the calendar.

// Target of the requirement is need to create to flags for MTD, YTD, QTD . This flags should effect to all the data in the application without using any set analysis. For now it is working MTD and YTD. Now we are looking for QTD to calculate like the Same.
Sub Calendar
Calendar:
// Since not having date field in the data, calendar is creating using the month field from fact.
LOAD Month Resident Fact;

Temp:
Load

//Hard coding the dates FROM and TO
min(makedate(2016,1,1)) as minDate,

max(makedate(2020,12,31)) as maxDate

Resident Calendar;

DROP Table Calendar;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

YTDCalendar:
Load Distinct
// Creating MTD flag using Tempdate
Month(TempDate) As Month,
Date(Date#(Month(TempDate),'MMM'),'MMM') as MonthDesc,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) as Quarter,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) &'_'& Date(Date#(Month(TempDate),'MMM'),'MMM') as QtrMonth,

'MTD' as PeriodScope

Resident TempCalendar;

Concatenate (YTDCalendar)
Load
Month(TempDate) As Month,

// Creating QTD flag using Tempdate
Date(Date#(Month(TempDate),'MMM'),'MMM') as MonthDesc,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) as Quarter,
'Q'&Ceil(Num(Month(AddMonths(TempDate,0)))/3) &'_'& Date(Date#(Month(TempDate),'MMM'),'MMM') as QtrMonth,

// While creating QTD flag, we have been used today() function so it is effecting only to current Quarter. Can we achieve using any other logic, QTD should effect to all the 12 months?
If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()) and Ceil(Month(TempDate)/3) = Ceil(Month(Today())/3), 'QTD') as PeriodScope

Resident TempCalendar;

//Creating YTD flag using IterNo()
Concatenate(YTDCalendar)
LOAD
IterNo() as Month,
MonthDesc,
QtrMonth,
Quarter,
'YTD' as PeriodScope
Resident YTDCalendar
While IterNo() <= Month
;

Drop Table TempCalendar;

END SUB

[edwin]

this is not quarter calculation, if March is selected, Jan, Feb, and Mar are possible selections; if Feb is selected only Jan and Feb are possible selections.  since you r data is at month level, your requirement isnt really QTD but Quarter to Month.  QTD means it is from first to the quarter to the actual date and not month