Hi,
I struggle to find a correct title...
I have sales in different files : Actuals 2011, Plan (budget) 2012, Actuals 2012, etc.
The load is as follow for all files:
Directory;
CrossTable(Month, Sales2012Plan, 4)
LOAD COUNTRY,
[PRODUCT NAME],
Year,
[Sales source],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[APAC Sales Report - 2012 Plan.xls]
(biff, embedded labels, table is Database$);
what differs are the parts in red.
I have loaded those files to allow me to do graph 1 (see attachment)
Now as I wanted to do the graph 2, I could not find a way to do it with 2011 months followed by 2012 months.
So I reloaded all the exact same tables with the following changes in blue
for budget data :
Directory;
CrossTable(Monthactual, salesplan, 2)
LOAD COUNTRY,
[PRODUCT NAME],
Jan as [Jan-12],
Feb as [Feb-12],
Mar as [Mar-12],
Apr as [Apr-12],
May as [May-12],
Jun as [Jun-12],
Jul as [Jul-12],
Aug as [Aug-12],
Sep as [Sep-12],
Oct as [Oct-12],
Nov as [Nov-12],
Dec as [Dec-12]
FROM
[APAC Sales Report - 2012 Plan.xls]
(biff, embedded labels, table is Database$, filters(
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 3))
));
For actuals:
Directory;
CrossTable(Monthactual, salesactual, 2)
LOAD COUNTRY,
[PRODUCT NAME],
Jan as [Jan-12],
Feb as [Feb-12],
Mar as [Mar-12],
Apr as [Apr-12],
May as [May-12],
Jun as [Jun-12],
Jul as [Jul-12],
Aug as [Aug-12],
Sep as [Sep-12],
Oct as [Oct-12],
Nov as [Nov-12],
Dec as [Dec-12]
FROM
[APAC Sales Report - 2012 LBE.xls]
(biff, embedded labels, table is Database$, filters(
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 3))
));
I am convinced that there must be a much simpler way (as I have 12000 lines per table and I am loading now 2x 6 tables with some changes + crosstables) it takes for ever.
Please help.
Thnks
