Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
ForeCast: LOAD [Acc Type], //Source, Activity, Product, [31/10/2018] AS [1], [30/11/2018] AS [2], [31/12/2018] AS [3], [31/01/2019] AS [4], [28/02/2019] AS [5], [31/03/2019] AS [6], [30/04/2019] AS [7], [31/05/2019] AS [8], [30/06/2019] AS [9], [31/07/2019] AS [10], [31/08/2019] AS [11], [30/09/2019] AS [12] FROM [Plan.xlsx] (ooxml, embedded labels, table is [Forecast ]); YTD: CrossTable(%CalKey, Amount,5) LOAD [Acc Type], 'Forecast' as Source, 'Actual Forecast' as Scenario, Activity, Product, [1]*$(v1) as '31/10/2018', [1]*$(v1)+[2]*$(v2) as '30/11/2018', [1]*$(v1)+[2]*$(v2)+[3]*$(v3) as '31/12/2018', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4) as '31/01/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5) as '28/02/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6) as '31/03/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7) as '30/04/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8) as '31/05/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9) as '30/06/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) as '31/07/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v11) as '31/08/2019', [1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v10) +[12]*$(v12) as '30/09/2019' Resident ForeCast where [Acc Type]='AVG'; Drop Table ForeCast;
Hi Experts,
Please let me know what is the issue with the code.
Can we not perform a crosstable on a resident load.
Hi Silambarasan,
Not working still.
Try it with:
ForeCast:
LOAD [Acc Type], 'Forecast' as Source, 'Actual Forecast' as Scenario, Activity, Product,
[1]*$(v1) as '31/10/2018',
[1]*$(v1)+[2]*$(v2) as '30/11/2018',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3) as '31/12/2018',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4) as '31/01/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5) as '28/02/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6) as '31/03/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7) as '30/04/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8) as '31/05/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9) as '30/06/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) as '31/07/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v11) as '31/08/2019',
[1]*$(v1)+[2]*$(v2)+[3]*$(v3)+[4]*$(v4)+[5]*$(v5)+[6]*$(v6)+[7]*$(v7)+[8]*$(v8)+[9]*$(v9)+[10]*$(v10) +[11]*$(v10) +[12]*$(v12) as '30/09/2019';
LOAD [Acc Type], Activity, Product,
[31/10/2018] AS [1], [30/11/2018] AS [2], [31/12/2018] AS [3], [31/01/2019] AS [4],
[28/02/2019] AS [5], [31/03/2019] AS [6], [30/04/2019] AS [7], [31/05/2019] AS [8],
[30/06/2019] AS [9], [31/07/2019] AS [10], [31/08/2019] AS [11], [30/09/2019] AS [12]
FROM [Plan.xlsx] (ooxml, embedded labels, table is [Forecast ]);
YTD:
CrossTable(%CalKey, Amount,5) load * Resident ForeCast where [Acc Type]='AVG';
Drop Table ForeCast;
I think your issue isn't caused from the resident else that a crosstable treated all content as strings and didn't evaluated anything.
- Marcus