# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor III

## YTD Calculation

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,

9 Replies
MVP

## Re: YTD Calculation

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)))"}>}

New Contributor III

## Re: YTD Calculation

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?

New Contributor III

## Re: YTD Calculation

sunindia‌ Can you help?

MVP

## Re: YTD Calculation

Working on it

New Contributor III

## Re: YTD Calculation

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

MVP

## Re: YTD Calculation

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) +

(Sales of Dec14+Sales for Jan15)/2

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

See if the above helps.

Best,

Sunny

MVP

## Re: YTD Calculation

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

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

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

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

(Sales of Dec13+Sales for Jan14)/2

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

New Contributor III

## Re: YTD Calculation

Sunny,

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

New Contributor III

## Re: YTD Calculation

Anyone have idea how to calculate it dynamically?