topic Run Rate in a chart in QlikView

Run Rate in a chart

Tue, 30 Oct 2012 15:24:11 GMT

Hi,

I need to achieve the following goal:

In the Days Factor column (see the bellow table), the calculation is [num of days in month] / [current day]

so, the [num of days in month] for November is 31 days, and the [current day] is changing according to the day in the table record.

For example, on 2012-10-27 the calculation will be 31 / 27 = 1.15

First, I dont know how to calculate this column. How can i determine which is the [current day] in specific record?

The Accumulating Revenue is actually accumulating the Revenue up to date, so, for example, on 2012-10-29 the revenue is the sum of 2012-10-27, 2012-10-28 and 2012-10-29

Second, How can i calculating this column? I can use the Accumulating checkbox for this column, but we must remember that i still need to use this value for the RunRate Revenue calculation.

The RunRate Revenue is the multiply of [Days Factor] * [Accumalting Revenue].

Date	Days Factor	Accumalting Revenue	RunRate Revenue
2012-10-27	1.15	$100	$115
2012-10-28	1.11	$150	$166
2012-10-29	1.07	$170	$182
2012-10-30	1.03	$200	$207
2012-10-31	1.00	$210	$210

How can I perform this calculation ?

Thanks,

Guy

Re: Run Rate in a chart

Anonymous — Tue, 30 Oct 2012 15:30:42 GMT

i 'm sorry that i couldn't get your requiremnt

you said --> runrate revenue = dayfactor * runrate revenue

if runrate revenue = dayfactor * accumulating revenue

then u can use this expression ---> coulumn(1)* column(2)

Re: Run Rate in a chart

Tue, 30 Oct 2012 15:58:29 GMT

Hi,

I updated my question since it was not clear at first. Yes the RunRate Revenue is the multiply of [Days Factor] * [Accumulating Revenue], but still i don't know how to calculate [Days Factor] , because i can't determine the row day inside my table, and also i don't know how to accumulate the revenue. after i know how to do it, i will be able to calculate the RunRate Revenue.

Thanks,

Guy

Re: Run Rate in a chart

christophebrault — Tue, 30 Oct 2012 16:10:50 GMT

hi,

for Days Factor, you can do :

2-(Day(Date)/day(MonthEnd(Date)))

to calculate accumulation in a straight table you can write :

RangeSum(Above(sum( [Accumulating Revenue]),0,rowno()))

I'm not testing at the moment but this should work.

Finally, you can reuse this 2 expression in the third column

Re: Run Rate in a chart

Wed, 31 Oct 2012 09:57:15 GMT

Hi Christopher,

Yes, it works fine!

Thank you very much,

Guy

Re: Run Rate in a chart

christophebrault — Wed, 31 Oct 2012 11:18:25 GMT

Good news !

Bye