topic Re: YTD Calculation in QlikView

YTD Calculation

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends,

I want to calculate YTD value?

How, Can anyone help me please

I tried this

=sum({$<Year=,Month=,C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

If i am using this, It is working. But, while i trying to create whole year and whole month it is not working

=sum({$<Year={2016},Month={1},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

Re: YTD Calculation

settu_periasamy — Fri, 22 Jul 2016 20:39:50 GMT

Hi Anil,

Just to confirm, how your Month filed looks?

did you try to remove the Month field from the set expression, is that worked?

Re: YTD Calculation

ziadm — Fri, 22 Jul 2016 21:31:44 GMT

The best practice is to create a Master Calendar Table having all your Dimension such as YTD, MTD, WTD

and the use YTD flag in your set analysis to get your metrics

you may use the following code

MinMax:

LOAD

Min(DATEEDIT) as MinDate,

Max(DATEEDIT) as MaxDate

RESIDENT SALESTable;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//LET vToday = Num(Today(0));

///*************** Temporary Calendar *************

//Generates a single table with one field containing

//all existing dates between MinDate and MaxDate.

//*/

////

TempCal:

LOAD

date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

MasterCalendar:

LOAD

TempDate AS CalendarDate,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

//Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inmonthtodate(TempDate, $(vToday), 0) * -1 AS MTDFlag,

inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

inmonthtodate(TempDate, addyears($(vToday),-1), 0) * -1 AS LastYMTDFlag,

inyeartodate(TempDate, addyears($(vToday),-1), 0) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: YTD Calculation

Anil_Babu_Samineni — Sat, 23 Jul 2016 04:03:01 GMT

My Month and Year fields looks like

Date Field - S_CREATED_DATE,

year(S_CREATED_DATE) as Year,

num(Month(S_CREATED_DATE)) as Month,

And after your reply i use this expression

=sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

I filter for 2016 and 4th Month

The image looks like this. Why Month and YTD values are same because we are using same expression for Month and YTD. But, here i want to calculate YTD, How, I don't know?

Re: YTD Calculation

Anil_Babu_Samineni — Sat, 23 Jul 2016 04:04:15 GMT

Do we required Master Calendar to create the YTD for this situation. If i created same way then how to calculate YTD?

Help Me!!

Re: YTD Calculation

maxgro — Sat, 23 Jul 2016 06:39:02 GMT

one solution is to add a flag field (see Ziad Mohammad 's answer or look at the InYearToDate function) to your date, year, etc...... fields

InYearToDate(YourDateField, today(), 0) as CurYTDFlag

and use that field to filter

=sum({$<CurYTDFlag={1}, Year=,Month=,C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

another solution, without adding fields to the script is to filter the date with set sanalysis (add the other fields to the expression)

sum({$ <Month=,Year=,YourDateField={"<=$(=today())"}>} val)

sum({$ <Month=,Year=,YourDateField={"<=$(=date(today(), 'DD/MM/YYYY'))"}>} val)

replace DD/MM/YYYY with your date format

Re: YTD Calculation

Anil_Babu_Samineni — Sat, 23 Jul 2016 10:08:23 GMT

This is My Calendar, My Values are not coming

SafetyCounter_dbo_3BSafety:

S_CREATED_DATE

//Fresh Calendar

MinMax:

LOAD

Min(S_CREATED_DATE) as MinDate,

Max(S_CREATED_DATE) as MaxDate

RESIDENT SafetyCounter_dbo_3BSafety;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

TempCal:

LOAD

date($(vMinDate) + rowno() - 1) AS S_CREATED_DATE

AUTOGENERATE

$(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

MasterCalendar:

LOAD

S_CREATED_DATE AS CalendarDate,

Week(S_CREATED_DATE) AS Week,

Year(S_CREATED_DATE) AS Year,

Num(Month(S_CREATED_DATE)) AS Month,

Day(S_CREATED_DATE) AS Day,

Weekday(S_CREATED_DATE) AS WeekDay,

'Q' & ceil(month(S_CREATED_DATE) / 3) AS Quarter,

//Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

Week(S_CREATED_DATE)&'-'&Year(S_CREATED_DATE) AS WeekYear,

inmonthtodate(S_CREATED_DATE, $(vToday), 0) * -1 AS MTDFlag,

inyeartodate(S_CREATED_DATE, $(vToday), 0) * -1 AS CurYTDFlag,

inmonthtodate(S_CREATED_DATE, addyears($(vToday),-1), 0) * -1 AS LastYMTDFlag,

inyeartodate(S_CREATED_DATE, addyears($(vToday),-1), 0) * -1 AS LastYTDFlag

RESIDENT TempCal;

//ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: YTD Calculation

Tue, 09 Aug 2016 21:14:00 GMT

Give 2016 & 1 in single quotes.