topic Re: Dimension and Expression formula in QlikView

Dimension and Expression formula

nayanpatel — Sat, 21 Nov 2015 15:38:57 GMT

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

Re: Dimension and Expression formula

MK_QSL — Sat, 21 Nov 2015 17:12:51 GMT

Use Pivot Table...

Re: Dimension and Expression formula

nayanpatel — Sun, 22 Nov 2015 06:34:05 GMT

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

Re: Dimension and Expression formula

bariyb23 — Sun, 22 Nov 2015 07:56:13 GMT

Hope this will help

Re: Dimension and Expression formula

effinty2112 — Sun, 22 Nov 2015 08:50:11 GMT

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

Re: Dimension and Expression formula

nayanpatel — Tue, 24 Nov 2015 08:28:14 GMT

Hi Andrew,

Thanks its like that only.