topic Re: YTD Calculation in QlikView

YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 03:01:53 GMT

Hi,

I would like to calculate YTD in the following way.

Ex: if my current selection is 2015 March, then the ytd calculation should be like below.

sales for Mar 15+Sales for Feb 15+Sales for Jan15+sales for Jan14+Sales for Feb14+Sales for Mar14

if I select jan 2015, then it should be

sales for jan 15+sales for jan 14 alone

How can I do this,

Re: YTD Calculation

sunny_talwar — Thu, 30 Jul 2015 03:08:01 GMT

May be like this in your set analysis

{<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"} + {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"}>}

Re: YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 03:14:30 GMT

Thanks sunny for the reply.

Small correction: if my current selection is 2015 then the formula should be like

(sales for Mar 15+Sales for Feb 15)/2+(Sales for Feb 15+Sales for Jan15)/2+(Sales of Dec15+Sales for Jan15)/2

(sales for Mar 14+Sales for Feb 14)/2+(Sales for Feb 14+Sales for Jan14)/2+(Sales of Dec14+Sales for Jan14)/2

in this case, how can I do?

Re: YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 03:47:58 GMT

sunindia‌ Can you help?

Re: YTD Calculation

sunny_talwar — Thu, 30 Jul 2015 04:00:23 GMT

Working on it

Re: YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 04:01:46 GMT

The problem is I have to consider each month as (month+priormonth)/2..so for feb..(jan+feb)/2..

not sure how to calculate ytd in this case

Re: YTD Calculation

sunny_talwar — Thu, 30 Jul 2015 04:08:05 GMT

May be like this:

(sales for Mar 15+Sales for Feb 15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' & Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales))/2

(Sales for Feb 15+Sales for Jan15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales))/2

(Sales of Dec14+Sales for Jan15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -3))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -3))))"} Sales))/2

See if the above helps.

Best,

Sunny

Re: YTD Calculation

sunny_talwar — Thu, 30 Jul 2015 04:13:44 GMT

(sales for Mar 14+Sales for Feb 14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -12))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -12))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales))/2

(Sales for Feb 14+Sales for Jan14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales))/2

(Sales of Dec13+Sales for Jan14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -15))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -15))))"} Sales))/2

Re: YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 04:36:04 GMT

Sunny,

So I have to create the formula for each month? is there any other way to dynamically?

Re: YTD Calculation

gauthamchilled — Thu, 30 Jul 2015 05:07:59 GMT

Anyone have idea how to calculate it dynamically?