Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD Calculation

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.

6 Replies
jaspal_icon
Partner - Creator
Partner - Creator

try this:

Sum(

{

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

Amount

   )

Not applicable
Author

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.

SunilChauhan
Champion
Champion

use below code month should in number

Sum(

{

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

Amount

   )

hope this helps

Sunil Chauhan
v_iyyappan
Specialist
Specialist

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.

Not applicable
Author

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

jaspal_icon
Partner - Creator
Partner - Creator

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