topic Re: Forecasting in QlikView

Forecasting

Mon, 03 Sep 2012 14:34:21 GMT

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

Re: Forecasting

whiteline — Mon, 03 Sep 2012 14:43:30 GMT

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

=Referrals/above(Referrals)

Re: Forecasting

Anonymous — Mon, 03 Sep 2012 14:45:10 GMT

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

Re: Forecasting

Anonymous — Mon, 03 Sep 2012 14:46:46 GMT

How about something like this:

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

Re: Forecasting

Anonymous — Mon, 03 Sep 2012 14:51:55 GMT

Aaron,

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

Regards,

Michael

Re: Forecasting

Mon, 03 Sep 2012 15:21:24 GMT

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

Re: Forecasting

whiteline — Mon, 03 Sep 2012 15:37:02 GMT

you should use your expression for Referals inside above()

Re: Forecasting

Mon, 03 Sep 2012 16:13:12 GMT

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

Re: Forecasting

Anonymous — Mon, 03 Sep 2012 16:29:45 GMT

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

Re: Forecasting

whiteline — Tue, 04 Sep 2012 04:08:34 GMT

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.

Re: Forecasting

Tue, 04 Sep 2012 09:50:33 GMT

THank you so much for all your help and assistance. Its been very helpful.

However I'm thinking a different approach now.

Month	2010	2011	% Increase
Apr	329	380	15.50%
May	329	380	15.50%
Jun	329	380	15.50%
Jul	329	380	15.50%
Aug	329	380	15.50%
Sep	329	380	15.50%
Oct	329	380	15.50%
Nov	329	380	15.50%
Dec	329	380	15.50%
Jan	329	380	15.50%
Feb	329	380	15.50%
Mar	329	380	15.50%

Its just dummy data, I can do this for one month by using creating an expression:

count({<Referral.MonthYear={'Apr-10'}>} Referral.PatientId_Value)

But how would I do this for every month?

Sorry for changing my approach but its all good learning

Cheers

Re: Forecasting

whiteline — Tue, 04 Sep 2012 09:56:46 GMT

But how would I do this...

Could you explain a little ?

Maybe, this one could help:

=count({<Referral.MonthYear={'Apr-10'}>} total Referral.PatientId_Value)

Re: Forecasting

Tue, 04 Sep 2012 11:27:16 GMT

Hi,

Sorry I'll start again:

I have data in this format:

PaitientID	ReferralDate
1	10/04/2010
2	11/05/2010
3	11/06/2010
4	12/07/2010
5	12/08/2010
6	12/09/2010
7	13/10/2010
8	13/11/2010
9	14/12/2010
10	14/01/2011
11	14/02/2011
12	17/03/2011
13	17/04/2011
14	18/05/2011
15	18/06/2011
16	19/07/2011
17	19/08/2011
18	19/09/2011
19	20/10/2011
20	20/11/2011
21	21/12/2011
22	21/01/2012
23	21/02/2012
24	23/03/2012
25	23/04/2012
26	24/05/2012
27	24/06/2012

And I want to reporesent the data in this format Counting the number of referrralds (this dummy data which I have just copied hence all rows the same numbers)

Month	2010	2011
Apr	329	380
May	329	380
Jun	329	380
Jul	329	380
Aug	329	380
Sep	329	380
Oct	329	380
Nov	329	380
Dec	329	380
Jan	329	380
Feb	329	380
Mar	329	380

Hope this helps.

Cheers

Re: Forecasting

whiteline — Tue, 04 Sep 2012 14:59:18 GMT

First, you have to load Years as a separate field in the script:

Year(ReferralDate) as Year

Then create chart with dimensions:

Month: =Date(MakeDate(0,Month(ReferralDate)), 'MMM')

Year: Year

Expression: =Count(total<Year>PaitientID)

Then pull the year dimension so that the blue arrow become horizontal.

You need a separate year field for total<Year>