topic Re: YTD Computation in QlikView

YTD Computation

Wed, 02 Apr 2014 06:20:29 GMT

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

Re: YTD Computation

tresB — Wed, 02 Apr 2014 06:23:42 GMT

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().

Re: YTD Computation

its_anandrjs — Wed, 02 Apr 2014 06:28:05 GMT

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

Re: YTD Computation

Wed, 02 Apr 2014 07:37:08 GMT

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!

Re: YTD Computation

Thu, 03 Apr 2014 01:28:59 GMT

bump

Re: YTD Computation

Thu, 03 Apr 2014 04:49:10 GMT

bump

Re: YTD Computation

tresB — Thu, 03 Apr 2014 04:51:56 GMT

Could you post a sample app explaining expected output there?

Re: YTD Computation

Thu, 03 Apr 2014 05:06:50 GMT

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

Re: YTD Computation

jonathandienst — Thu, 03 Apr 2014 05:32:58 GMT

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

Re: YTD Computation

rohit214 — Thu, 03 Apr 2014 05:36:13 GMT

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

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

Thanks

Rohit