topic Re: How to calculate YTD in QlikView

How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 14:53:43 GMT

Hi Guys,

I have a requirement that i need to calculate YTD for a data

Year To Date (YTD):

Dimensions: Year

I need to show all the Year

suppose if i click Year =2013 & Month= Jun , The chart has to show like sum(sales) from Jan to Jun for all the year

i.e.,

2013 = Jan to Jun

2012 = Jan to Jun

2011 = Jan to Jun

Attached the sample data

Re: How to calculate YTD

girirajsinh — Mon, 23 Jan 2017 15:12:09 GMT

Based on dimension you have shared in excel i.e. YearMonth

Sum({$<YearMonth = {"<=$(=Max(YearMonth))"},Year = {$(=Max(Year))} >} Sales)

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 15:18:18 GMT

May be like this?

Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales)

Where MonthNum is created in script like this:

Sample:

LOAD YearMonth,

Year(YearMonth) as Year,

Month(YearMonth) as Month,

Num(Month(YearMonth)) as MonthNum,

Sales

FROM

[SAMPLE.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 15:21:39 GMT

Sorry Guys

I missed a important thing in my requirement

This YTD calculation of current Year YTD vs Previous Year YTD

formula is : (Current Year YTD - Previous Year YTD) / Previous Year YTD

Current Year YTD : 2013 (Jan - Sep)

Previous Year YTD: 2012 (Jan - Sep)

and values should be plotted in 2013 like for others years

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 15:27:09 GMT

Hi Sunny,

It is similar to previous as you helped me last time but this time in year

have a look in the below:

This YTD calculation of current Year YTD vs Previous Year YTD

formula is : (Current Year YTD - Previous Year YTD) / Previous Year YTD

Current Year YTD : 2013 (Jan - Sep)

Previous Year YTD: 2012 (Jan - Sep)

and values should be plotted in 2013 like for others years

Re: How to calculate YTD

MK_QSL — Mon, 23 Jan 2017 15:37:56 GMT

Try this..

(Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales)

Above(Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales)))

Above(Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales))

EDITED : Haven't created whole expression myself. I have used expression from stalwar1‌'s reply.

Re: How to calculate YTD

neelamsaroha157 — Mon, 23 Jan 2017 15:40:35 GMT

Try this

(Sum({< MonthNum = {"<=$(=Max(MonthNum))"}, Year = {$(=Max(Year))}, Month= , YearMonth=>}Sales)

Sum({< MonthNum = {"<=$(=Max(MonthNum))"}, Year = {$(=Max(Year)-1)}, Month= , YearMonth=>}Sales)

)

Sum({< MonthNum = {"<=$(=Max(MonthNum))"}, Year = {$(=Max(Year)-1)}, Month= , YearMonth=>}Sales)

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 15:45:38 GMT

I believe this should do it....

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 16:50:55 GMT

Hi Manish,

your expression is working

but only one when i click on 2012 it is still showing the 2013 value also

i.e., if i click on 2013 the bar chart will 2011 to 2013

if i click on 2012 the bar chart has to display 2011 to 2012 but not 2011 to 2013

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 16:53:59 GMT

Wait what? You have completely confused me Hemanth....

When you click on 2013 you want 2013/2011 - 1

and when you click on 2012 you want 2012/2011 - 1?

Is this the requirement?

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 16:54:58 GMT

Sunny,

your expression to working fine and view also as per my requirement

but only one thing is no change in previous years

i.e Suppose if i click Year = 2013 and Month= Apr

previous years also has to show from same month

As per your expression current year is changing perfectly but there is no change in value in previous year

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 16:58:35 GMT

I think it is changing...

What is the output you expect when you select 2013 and April?

Also, I have not used a new expression. It is the same one as the one that Manish gave.

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 17:00:22 GMT

No No sunny,

iam not saying about the formula

iam saying about view of bar chart

if i click on Year =2013 and month = Apr

in barchart:

2011(Jan- Apr) ,2012 (Jan- Apr), 2013(Jan - Apr) that means three bars has to come out in the chart

if i click on Year =2012 and month = Apr

in barchart :

2011(Jan- Apr) ,2012 (Jan- Apr), that means two bars has to come out in chart

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 17:10:01 GMT

Sunny

Please have a look on the attachment

In the chart the expression give required values but only one draw back is

if i click on Year =2012 In the bar chart 2013 also visible which i dnt want to show in bar chart

i.e,

when click on year= 2013 and month =Jun

bars in the chart should

2013= jan to jun sum(sales)

2012 = jan to jun sum(sales)

2011= jan to jun sum(sales)

when click on year =2012 and month = jun

bar in the chart

2012 = jan to jun sum(sales)

2011= jan to jun sum(sales)

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 17:12:36 GMT

May be this?

(Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales)

Above(Sum({<Month, Year, YearMonth, MonthNum = {"$(='>=1<=' & Max(MonthNum))"}>}Sales)))-1

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 17:28:02 GMT

I that bar chart if u observe in the screen shot

I think no sunny ,

in the First & Second screen shot 2011 & 2012 values are same which is to be not like that

My required r expect output is it has to change based on the selection month in all charts

As in Manish expression it is changing but only one thing when i select on 2012 but it still displaying the 2013 too

Re: How to calculate YTD

sunny_talwar — Mon, 23 Jan 2017 17:33:55 GMT

Where are you seeing this? In the file that i attached? I see this

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 17:40:33 GMT

Exactly Sunny ,

It is working fine

Thank you very much Sunny

Cheersss...!

Re: How to calculate YTD

hemanthaanichet — Mon, 23 Jan 2017 17:44:49 GMT

Yeah I was a bit delay in finding the new attachment

when i was about to explain you a bit clear in that mean time you solved it and posted

Thanks Sunny,