Dimension and Expression formula

[nayanpatel]

Hi Friends,

Need your input to get it done.

I have 3 expressions and 2 dimensions. Now I need output like attached image.

It contains Data image and output image.

Please help to get it done.

Thanks

[MK_QSL]

Use Pivot Table...

[nayanpatel]

Dear Manish,

Can you please check once what exact my need is? I am using pivot table only, however my need is to minus total data from one Dimension to total data from another dimension. please check

[bariyb23]

Hope this will help

[effinty2112]

Hi Nayan,

I think you need to do some work in the script like this:

Data:

LOAD * INLINE [

    Name, Gain, Investment, Loss, Year

    Paul, 20000, 2000, 7000, 2015

    Tinn, 10000, 3000, 2000, 2015

    Paul, 30000, 3000, 2000, 2015

    Tinn, 25000, 5000, 7000, 2015

];

NoConcatenate

[Paul - Tinn]:

Load

Name,

sum(Gain) as Gain,

sum(Investment) as Investment,

sum(Loss) as Loss,

Year

Group by Name, Year;

Load

'Paul - Tinn' as Name,

if(Name = 'Tinn',-Gain,Gain) as Gain,

if(Name = 'Tinn',-Investment,Investment) as Investment,

if(Name = 'Tinn',-Loss,Loss) as Loss,

Year

Resident Data;

Concatenate(Data)

LOAD * Resident [Paul - Tinn];

DROP Table [Paul - Tinn];

Now you can create a pivot table like this:

Year	2015
Name	Paul	Tinn	Paul - Tinn
Gain	50000	35000	15000
Investment	5000	8000	-3000
Loss	9000	9000	0

[nayanpatel]

Hi Andrew,

Thanks its like that only.