Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
I am trying to add a new variable to Cross Table and encountered following problems, please help :
1. Script not working after adding new variable (Profit)
Report:
CrossTable(Year, Data, 3)
LOAD
Revenue-[Cost of Goods] as [Profit],
* FROM
[Test-1.xlsx]
(ooxml, embedded labels, table is Data);
2. Wrong % computation, please refer to correct answer in Excel file attached.
Thank you
Tracy
Can use Peek to calculate remaining info after adding Profit as new desc.
TableA:
LOAD Company,
Description,
[2012],
[2013],
[2014]
FROM
(ooxml, embedded labels, table is Data);
outer Join(TableA)
Load distinct
Company,
'Profit' as Description,
0 as [2012],
0 as [2013],
0 as [2014]
Resident TableA;
TableB:
Load Company,
Description,
if(Description<>'Profit',[2012],Peek([2012],-2)-Peek([2012],-1)) as [2012],
if(Description='Revenue',num([2012]/[2012],'##.0%'),
if(Description='Cost of Goods',num([2012]/peek([2012],-1),'##.0%'),num((Peek([2012],-2)-Peek([2012],-1))/Peek([2012],-2),'##.0%'))) as [2012 %],
if(Description<>'Profit',[2013],Peek([2013],-2)-Peek([2013],-1)) as [2013],
if(Description='Revenue',num([2013]/[2013],'##.0%'),
if(Description='Cost of Goods',num([2013]/peek([2013],-1),'##.0%'),num((Peek([2013],-2)-Peek([2013],-1))/Peek([2013],-2),'##.0%'))) as [2013 %],
if(Description<>'Profit',[2014],Peek([2014],-2)-Peek([2014],-1)) as [2014],
if(Description='Revenue',num([2014]/[2014],'##.0%'),
if(Description='Cost of Goods',num([2014]/peek([2014],-1),'##.0%'),num((Peek([2014],-2)-Peek([2014],-1))/Peek([2014],-2),'##.0%'))) as [2014 %]
Resident TableA order By Company;
Drop Table TableA;
Hi,
May be you can try this in front, after loading the cross table. like
CrossTable(Year, Data, 2)
LOAD Company,
Description,
[2012],
[2013],
[2014]
FROM
[Test-1.xlsx]
(ooxml, embedded labels, table is Data);
Your syntax is fine for adding a field (not a variable), but [Revenue] and [Cost of Goods] do not exist in the source document. As for your %, you do not explain how you are calculating that in QV, so its impossible to say why its wrong.
Hi
Check This One if its ok for u
Revenue:
CrossTable(Year, Amount, 2)
LOAD Company,
'Revenue' as Description,
[2012],
[2013],
[2014]
FROM
(ooxml, embedded labels, table is Data)Where Description='Revenue';
COGS:
CrossTable(Year, Amount1, 2)
LOAD Company ,
'COGS' as Description1,
[2012],
[2013],
[2014]
FROM
(ooxml, embedded labels, table is Data)Where Description='Cost of Goods';
Concatenate(Revenue)
LOAD * Resident COGS;
DROP Table COGS;
NoConcatenate
Profit:
LOAD Company,Year, (sum(Revenue) - sum(COGS)) as Profit% Group by Company,Year ;
LOAD *,Amount AS Revenue, Amount1 as COGS Resident Revenue;
DROP Table Revenue;
Can use Peek to calculate remaining info after adding Profit as new desc.
TableA:
LOAD Company,
Description,
[2012],
[2013],
[2014]
FROM
(ooxml, embedded labels, table is Data);
outer Join(TableA)
Load distinct
Company,
'Profit' as Description,
0 as [2012],
0 as [2013],
0 as [2014]
Resident TableA;
TableB:
Load Company,
Description,
if(Description<>'Profit',[2012],Peek([2012],-2)-Peek([2012],-1)) as [2012],
if(Description='Revenue',num([2012]/[2012],'##.0%'),
if(Description='Cost of Goods',num([2012]/peek([2012],-1),'##.0%'),num((Peek([2012],-2)-Peek([2012],-1))/Peek([2012],-2),'##.0%'))) as [2012 %],
if(Description<>'Profit',[2013],Peek([2013],-2)-Peek([2013],-1)) as [2013],
if(Description='Revenue',num([2013]/[2013],'##.0%'),
if(Description='Cost of Goods',num([2013]/peek([2013],-1),'##.0%'),num((Peek([2013],-2)-Peek([2013],-1))/Peek([2013],-2),'##.0%'))) as [2013 %],
if(Description<>'Profit',[2014],Peek([2014],-2)-Peek([2014],-1)) as [2014],
if(Description='Revenue',num([2014]/[2014],'##.0%'),
if(Description='Cost of Goods',num([2014]/peek([2014],-1),'##.0%'),num((Peek([2014],-2)-Peek([2014],-1))/Peek([2014],-2),'##.0%'))) as [2014 %]
Resident TableA order By Company;
Drop Table TableA;