Forecasting

[#1]

Hi

I have a table as below

MonthYear	Referrals	Discharges	Extra Patients
Apr-11	658	608	50
May-11	825	715	110
Jun-11	866	809	57
Jul-11	564	569	-5
Aug-11	660	690	-30
Sep-11	686	622	64
Oct-11	619	589	30
Nov-11	533	487	46
Dec-11	371	341	30
Jan-12	480	399	81
Feb-12	503	481	22
Mar-12	519	477	42
Apr-12	523	537	-14
May-12	389	322	67
Jun-12	125	110	15

For the referrals column, how can I calcualte in Qlikview a percentage increase/decrease for each month?

Cheers

[whiteline]

If you already have month aggregated data and you want to calculate it in a table then something like this:

=Referrals/above(Referrals)

[Anonymous · Accepted solution]

Assuming that your table is the staright table, and it is always sorted by month, add this expression:
(sum(Referrals) - above(sum(Referrals))) / above(sum(Referrals))

And, don't forget to format it as %.

Regards,
Michael

[Anonymous]

How about something like this:

IF(RowNo()=1,0,(SUM(Referrals)/ABOVE(SUM(Referrals))-1))

[Anonymous]

Aaron,

Adding condition for the 1st row makes sense.  Although I'd probably use text 'N/A' instead of '0'. 

Regards,

Michael

[#6]

THank you for all your quick responses,

However it does not seem to work. I keep getting a '-'.

My referrals column is created by counting the number of clients I have.

If I just do an expression with Referrals to check that it work I get the right answer, but if I do above(Referrals) I get the '-'

Any ideas

Cheers

[whiteline]

you should use your expression for Referals inside above()

[#8]

Hi,

I worked out why its not working. It was due to the way I was handling my dates. Not sure why that was affecting it though and would love some advice on how to deal with dates.

I have the referral date which I need to group by month and year.

I dont want to group the date on load but for this table I would like the dates grouped.

Date

(DateReceived_Value, 'MMM-YY') as MonthYear

I use this code on load, and I thought the table would automactialy group all the same, but it still displays a row for every line. Any help would be great.

Cheers

[Anonymous]

date(monthstart(DateReceived_Value), 'MMM-YY') as MonthYear

[whiteline]

The expression

Date(DateReceived_Value, 'MMM-YY') as MonthYear

Changes only a text representation (format) of DateReceived_Value. While QlikView distinguishes them by numeric values. You should also discard the days, for example as Michael suggested above.