Add a new variable in Cross Table

[tracycrown]

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

[settu_periasamy]

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);

[jonathandienst]

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.

[nizamsha]

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;

[Digvijay_Singh · Accepted solution]

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;