topic Re: Add a new variable in Cross Table in QlikView

Add a new variable in Cross Table

tracycrown — Mon, 28 Sep 2015 05:22:07 GMT

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

Re: Add a new variable in Cross Table

settu_periasamy — Mon, 28 Sep 2015 05:57:26 GMT

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

Re: Add a new variable in Cross Table

jonathandienst — Mon, 28 Sep 2015 06:13:19 GMT

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.

Re: Add a new variable in Cross Table

nizamsha — Mon, 28 Sep 2015 06:40:18 GMT

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;

Re: Add a new variable in Cross Table

Digvijay_Singh — Mon, 28 Sep 2015 07:17:01 GMT

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;