topic Pro Rata Calaculation - Is very intresting in QlikView

Pro Rata Calaculation - Is very intresting

Mon, 13 Sep 2010 06:45:12 GMT

Hi all,

Please help me in calculation pro rata data for each month.

We are maintaining a flag for pro rata. We need to distribute the measure (Openings in attached excel) equally for each month from Month(today()) to Pro rata month.

I have given detail source data and output required in attached excel.

Thaks in advance for all of you.

Regards,

Veman

Pro Rata Calaculation - Is very intresting

Mon, 13 Sep 2010 07:19:28 GMT

Please find the updated excel.

Pro Rata Calaculation - Is very intresting

johnw — Tue, 14 Sep 2010 01:12:36 GMT

This isn't your exact situation, but it may give you some ideas. See attached.

Pro Rata Calaculation - Is very intresting

Tue, 14 Sep 2010 11:34:22 GMT

Hi John,

Thanks for your support but I need the exact situation. I am unable to implemnt this.

Regards,

Veman

Pro Rata Calaculation - Is very intresting

johnw — Tue, 14 Sep 2010 23:47:00 GMT


Veman Reddy wrote:Thanks for your support but I need the exact situation. I am unable to implemnt this.

OK, see attached for a solution for your exact situation then. Script below:

SET DateFormat='MMM-YY';

Source:
LOAD
Loc
,date(date#(Date,'MMM-YY')) as Date
,"Pro Rata"
,Openings
INLINE [
Loc,Date,Pro Rata,Openings
A,Sep-10,0,10
A,Oct-10,0,8
A,Nov-10,1,12
A,Dec-10,1,28
B,Sep-10,0,6
B,Oct-10,1,24
B,Nov-10,1,33
B,Dec-10,0,9
];

Temp:
NOCONCATENATE
LOAD
Loc as Location
,Date as Month
,Openings as "Direct Openings"
,0 as "Prorated Openings"
RESIDENT Source
WHERE "Pro Rata"=0
;
INNER JOIN (Source)
LOAD 1 as "Pro Rata"
AUTOGENERATE 1
;
CONCATENATE (Temp)
LOAD
Loc as Location
,date(addmonths(Date,1-iterno())) as Month
,0 as "Direct Openings"
,Openings/(year(Date)-year(today())+month(Date)-month(today())+1) as "Prorated Openings"
RESIDENT Source
WHILE addmonths(Date,1-iterno()) >= monthstart(today())
;

Final:
NOCONCATENATE
LOAD *
,"Direct Openings"+"Prorated Openings" as "Total Openings"
;
LOAD
Location
,Month
,sum("Direct Openings") as "Direct Openings"
,sum("Prorated Openings") as "Prorated Openings"
RESIDENT Temp
GROUP BY Location, Month
;

DROP TABLES
Source
,Temp
;

Pro Rata Calaculation - Is very intresting

Tue, 01 Mar 2011 17:51:39 GMT

awesome John.

thanks so much for this 😄