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]

Yes, you got my point. Since I am not having Date field in my data, whenever I selects March Jan,Feb, mar are possible selections like same for all 12 months.

[edwin · Accepted solution]

it is unusual that your Month field is a text field and you dont have a Date type field.  you may want to reconsider as there are a lot of functionality that can be applied to date types (sorting is one - you need to make your field a dual or create a second field so you can sort the months properly.)  also, once you need to address requirements for future date, your months will be screwed up.

having said that you can still implement a similar solution but it will be mostly hard coded:

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

you let the user select the field SelectMonth and it will automatically link to all months that is Quarter to the Month:

select Mar -> Jan, Feb, Mar
select May -> Apr, May
select start of quarter Oct -> Oct only

you can get away with hard coding as there will always only be 12 months and the relationship to Quarter to the month will never change (again assuming Month is not a DATE type but text)

MonthSeq will allow you to sort the months Jan -> Dec.  it will get a bit complicated when you are in March and the user requests sorting to start March -> Feb 

[edwin]

this is the programmatic way of doing it in case you are interested (same logic when you are using dates):

tmpMonths: 
load text(month(addmonths(date('1/1/2021'), iterno()-1))) as Month, iterno() as MonthSeq, 'Q' & (floor((iterno()+2)/3)) as Quarter
while iterno()<=12; load 1 AutoGenerate(1);

inner join (tmpMonths)
load Month as SelectMonth, MonthSeq as SelectSeq, Quarter as SelectQuarter
Resident tmpMonths;
NoConcatenate
Months:
load Month, MonthSeq, Quarter, SelectMonth
Resident tmpMonths
where MonthSeq<=SelectSeq and Quarter=SelectQuarter;

drop table tmpMonths;

[krishna20]

Hi Edwin,

Thank you very much for providing solution 🙂 It's made my day.

Absolutely working fine 😎 

[edwin]

np