topic Re: QTD calculation in Script in QlikView

QTD calculation in Script

krishna20 — Sat, 16 Nov 2024 17:38:12 GMT

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

Re: QTD calculation in Script

krishna20 — Fri, 08 Jan 2021 03:31:57 GMT

Any inputs??

Re: QTD calculation in Script

edwin — Fri, 08 Jan 2021 14:13:28 GMT

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

Re: QTD calculation in Script

edwin — Fri, 08 Jan 2021 14:16:04 GMT

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

Re: QTD calculation in Script

krishna20 — Sat, 09 Jan 2021 10:18:14 GMT

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

Re: QTD calculation in Script

Anil_Babu_Samineni — Sat, 09 Jan 2021 15:16:49 GMT

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

Re: QTD calculation in Script

edwin — Sun, 10 Jan 2021 03:20:31 GMT

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

Re: QTD calculation in Script

edwin — Sun, 10 Jan 2021 03:27:45 GMT

check this out

Re: QTD calculation in Script

krishna20 — Mon, 11 Jan 2021 05:25:09 GMT

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

Re: QTD calculation in Script

edwin — Mon, 11 Jan 2021 13:52:09 GMT

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

Re: QTD calculation in Script

krishna20 — Tue, 12 Jan 2021 06:56:55 GMT

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.

Re: QTD calculation in Script

edwin — Tue, 12 Jan 2021 14:21:43 GMT

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

Re: QTD calculation in Script

edwin — Tue, 12 Jan 2021 14:22:53 GMT

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;

Re: QTD calculation in Script

krishna20 — Wed, 13 Jan 2021 12:24:57 GMT

Hi Edwin,

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

Absolutely working fine 😎

Re: QTD calculation in Script

edwin — Wed, 13 Jan 2021 14:24:45 GMT

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

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

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

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

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

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