Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
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)
1 Solution

Accepted Solutions
edwin
Master
Master

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 

View solution in original post

14 Replies
krishna20
Specialist II
Specialist II
Author

Any inputs??

edwin
Master
Master

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
Master
Master

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

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:

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

 


 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
edwin
Master
Master

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
Master
Master

check this out

krishna20
Specialist II
Specialist II
Author

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
Master
Master

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