Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
krishna20
Specialist II
Specialist II

QTD calculation in Script

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:

JanQ1
FebQ1
MarQ1
AprQ2
MayQ2
JunQ2
JulQ3
AugQ3
SepQ3
OctQ4
NovQ4
DecQ4

 

Could anyone help me to get the correct logic.

Reagrds

Krishna

Labels (2)
14 Replies
krishna20
Specialist II
Specialist II
Author

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
Specialist III
Specialist III

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
Specialist III
Specialist III

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
Specialist II
Specialist II
Author

Hi Edwin,

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

Absolutely working fine 😎 

edwin
Specialist III
Specialist III

np