Revenue and Budget data

[Nagaraju_KCS]

Hi , I have a requirement to display actuals and budget for all months in a selected year, in which i have always one value selected in Year and Month fields.

Example:

if March is selected, Jan , Feb & Mar should display actuals whereas Apr to Dec should display budget numbers. Can anyone pls help on this?

Revenue:

Client	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
A	100	90	350	125	123	89	500	123
B	200	85	590	255	234	350	250	321
C	231	345	12	321	567	120	125	432
D	123	120	39	59	23	320	50	143

Budget: 

Client	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
A	123	89	250	120	900	350	125	111	888	999	140	180
B	24	43	250	200	85	345	120	222	777	100	150	190
C	567	120	125	231	345	12	321	333	666	120	160	200
D	23	320	50	123	120	39	59	444	555	130	170	210

My output should be Jan to Aug that should be Revenue Table, and remaining Sep to Dec should be Budget table.

Thanks in Advance...

[antoniotiman · Accepted solution]

Hi Raju,

See Attachment

TableA:
CrossTable(Month,Revenue)
LOAD * Inline [
Client Jan Feb Mar Apr May Jun Jul Aug
A 100 90 350 125 123 89 500 123
B 200 85 590 255 234 350 250 321
C 231 345 12 321 567 120 125 432
D 123 120 39 59 23 320 50 143
](delimiter is spaces);
TableB:
CrossTable(Month,Budget)
LOAD * Inline [
Client Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A 123 89 250 120 900 350 125 111 888 999 140 180
B 24 43 250 200 85 345 120 222 777 100 150 190
C 567 120 125 231 345 12 321 333 666 120 160 200
D 23 320 50 123 120 39 59 444 555 130 170 210
](delimiter is spaces);
LOAD *,Month(Date#(Month,'MMM')) as Month1;
Join (TableA) LOAD * Resident TableB;
Drop Table TableB;

Regards,

Antonio