YTD Computation

[#1]

Hi All!

Just gonna ask a question regarding the computation of YTD.

We all know that getting the YTD we'll be using a set analysis that limits the year,month,quarter..

Sum({<Year={$(=Max(Year))},MonthNum = {"<=$(=Max(MonthNum))"}, Month=, Quarter=>} [fieldname])

The Problem:

Since I'll be multiplying this formula to a specific field, let us say [cost per month], which gives me the

formula now to:

(sum(amount) - Sum({<Year={$(=Max(Year))},MonthNum = {"<=$(=Max(MonthNum))"}, Month=, Quarter=>} [fieldname])) * [cost per month]

Now, my [cost per month] is different per Month. let's say i tick the month of March.. the formula gives me the YTD (Jan,Feb and March totals) but since I tick March, it will only get the [cost per month] of March. How can I get the [cost per month] of Jan and Feb?

What approach should I do?

Thanks in advance,

Donna

[tresesco]

Try Like:

Sum({<Year={$(=Max(Year))},MonthNum = {"<=$(=Max(MonthNum))"}, Month=, Quarter=>} [fieldname] * [cost per month] )

I.e. take the [cost per month] part inside of sum().

[its_anandrjs]

Write like below and yes take Cost per month in sum part

Sum({<Year={"$(=Max(Year))"},MonthNum = {"<=$(=Max(MonthNum))"}, Month=, Quarter=>} [fieldname] * [cost per month])

[#4]

tresesco and Anand Chauhan

Unfortunately this doesn't work as this will alter my formula.

my original formula is getting the (amount-formula above)*[costpermonth]/1000.

will update the details on the post.

Thanks!

[#5]

bump

[#6]

bump

[tresesco]

Could you post a sample app explaining expected output there?

[#8]

Hi,

I can only send a visual on excel, below is an example for Jan-Feb

Month	Amt_Final	Volume	Total Cost of Sales	Amt_Final - Volume*Total Cost	(Amt_Final - Volume*Total Cost) / Volume
Jan	1,341,542,456	30,325,290	42.76	44,833,055.60	1.006122705
Feb	1,379,869,091	32,547,091	41.83	18,424,274.47

Since in qlikview we sum up the totals:

Qlikview Month (YTD)	Amt_Final	Volume	Total Cost of Sales	Amt_Final - Volume*SUM(Total Cost of Sales)
Jan-Feb	2,721,411,547	62,782,381	?

[jonathandienst]

Hi

Assuming month is the only dimension in the table, I suggest

=Sum(Aggr((Sum(amount) - Sum({<Year={$(=Max(Year))},MonthNum = {"<=$(=Max(MonthNum))"}, Month=, Quarter=>} [fieldname])) * [cost per month], Month))

HTH

Jonathan

[rohit214]

Hi

sum({<Year={$(=(max(Year))},Monthnum={"<=$(=max(MonthNum))"},Month=,Quater=>}[fieldname])

*

sum({<Year={$(max(Year))},MonthNum={"<=$(=Max(Monthnum))"},Month=,Quater=>}[cost per month])

Thanks

Rohit