topic Re: YTD Calculation in QlikView

YTD Calculation

Mon, 09 Apr 2012 05:24:18 GMT

Hi All, I have two sets of Data ( Previous Year sales and current year sales) on a monthly basis for eg- Jan11 to dec11 and jan12 to dec12 side by side . I need to calclulate YTD for them. For eg- Previous Year April sales to current year april sales. I need a solution so that it automatically calculates the remaining month's YTD whenever i put the data in them.

YTD Calculation

jaspal_icon — Mon, 09 Apr 2012 05:41:02 GMT

try this:

Sum(

{

<Year = {$(=Max(Year))}, Month = {$(=Month(vTodaysDate))}>}

Amount

)

YTD Calculation

Mon, 09 Apr 2012 06:04:38 GMT

Hi Jaspal- I tried the above set statement but its not working. Let me brief you a bit more about my YTD calculation its actually (Current Year Sales-Previous Year Sales)/Current Year Sales and this sales figure is a cumulative one. For eg- if its for the month of April..i need to add the figures from Jan to Apr last year and jan to Apr current yearand then do the calculation.

YTD Calculation

SunilChauhan — Mon, 09 Apr 2012 06:14:27 GMT

use below code month should in number

Sum(

{

<Year = {$(=Max(Year))}, Month = {"<=$(=max(Montthno))}">}

Amount

)

hope this helps

Re: YTD Calculation

v_iyyappan — Mon, 09 Apr 2012 11:24:52 GMT

Hi,

Use the expression in prevuious year when you select month it shows previous year current selection months data defaultly its show previous year whole data

= Sum({<Year=,

NumDate={'>=$(=(Num(YearStart(AddYears((selectedDate),-1))))) <=$(=(Num(AddYears((selectedDate),-1))))'}>}Data)

Use the expression in current year .

= Sum({<Year=,

NumDate={'>=$(=(Num(YearStart((selectedDate),-1))))

<=$(=(Num((selectedDate),-1)))'}>}Data)

Where selectedDate = MakeDate(Max(Year),Max(Month),Max(Day))

Regards,

Iyyappan.

Re: YTD Calculation

Wed, 16 May 2012 04:45:54 GMT

Hi All , Thanks for the replies. However i want to ask you regarding the above case. I have a situation in which i want YTD for 2 regions from Jan - Apr and for 1 region Jan- Mar. How do i go about it in a single set statement? Is there any way of doing that so that when i click on the region it automatically calculates for the above time span? In short- I want if the region is Europe or Asia, then calculate till Jan to Mar and if the region is North AMerica or any other region , then calculate till Jan to Apr. Thanks- Anirban

Re: YTD Calculation

jaspal_icon — Tue, 26 Jun 2012 07:23:47 GMT

Hi Anirban,

For a region you have a fix months range, which you already know. So you can have some variable say:

vStartDate and vEndDate

Now say you want to to Show 1 Jan 2012 to 31 Mar 2012

then set variable value:

vStartDate= Date(MakeDate('1-Jan'&vSelectedYear))

vEndDate=Date(MakeDate(if(Region=US,'31-Mar', if(Region=Europe, '30-Apr'))&vSelectedYear))

Now in your chart you can set the expression like:

Sum(If(SalesDate>=vStartDate and SalesDate<=vEndDate, Sales_Value))

Hope this will help

Regards

Jaspal