Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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