topic Re: Sum function use in table with reference to other table in QlikView

Sum function use in table with reference to other table

kushalthakral — Wed, 09 Apr 2014 17:26:13 GMT

Dear all

i am new to qlikview

i have some requirement like below

i have a table

Model	Import	Export	Total
A	0.00	62.78	62.78
B	77.83	35.17	113.00
C	0.00	86.96	86.96
D	0.00	0.90	0.90
E	1.39	0.01	1.40
F	122.35	61.09	183.44
	201.57	246.92	448.49

Requirement is like i have to calculate below expression

x=(33/(B's Total+C's Total)) * B's Import

and

y=(33/(B's Total+C's Total)) * C's Import

and then create a table like

Model	Import	Export	Total
A
B	x
C	y
D
E
F

Thanks in advance

Regards

Kushal Thakral

Re: Sum function use in table with reference to other table

its_anandrjs — Wed, 09 Apr 2014 17:38:31 GMT

New Update now check

Write like for

For X = (33/(sum(if(Model = 'B', Import)) + sum(if(Model = 'B', Export)) ) +

(sum(if(Model = 'C', Import)) + sum(if(Model = 'C', Export)) ) ) *

sum(if(Model = 'B', Import))

For Y = (33/(sum(if(Model = 'B', Import)) + sum(if(Model = 'B', Export))) +

(sum(if(Model = 'C', Import)) + sum(if(Model = 'C', Export))) )*

sum(if(Model = 'C', Import))

And in straight table

Dimension :- Model

Expression1 :- X

Expression2 : Y

And in presentation unchecked Suppress Zero-Values

in place of X and Y you can use variables like

Let X = Definition;

Let Y = Definition;

and use it in the charts

Regards

Re: Sum function use in table with reference to other table

maxgro — Wed, 09 Apr 2014 17:51:47 GMT

this?

Re: Sum function use in table with reference to other table

kushalthakral — Wed, 09 Apr 2014 18:01:44 GMT

Hi Massimo

Thanks for ur efforts

but as per the calculation result comes out to be 12.84 for B and in ur table it is 22

Can you please help me to get correct data

Thanks

Re: Sum function use in table with reference to other table

its_anandrjs — Wed, 09 Apr 2014 18:25:45 GMT

Hi Kushal,

With SET analysis it will works better please check now with new updates load your table like

Data:

LOAD * Inline

[

Model,Import,Export,Total

A, 0.00, 62.78, 62.78

B, 77.83, 35.17, 113.00

C, 0.00, 86.96, 86.96

D, 0.00, 0.90, 0.90

E, 1.39, 0.01, 1.40

F, 122.35, 61.09, 183.44

];

And take straight table

Dimension:- Model

Expression for Import :-

=if(Model='B', (33 / (sum({<Model={'B','C'}>} Import) + sum({<Model={'B','C'}>} Export)))

* sum({<Model={'B'}>} Import),

(33 / (sum({<Model={'B','C'}>} Import) + sum({<Model={'B','C'}>} Export)))

* sum({<Model={'C'}>} Import))

Expression for Export :- Null();

Expression for Total :- Null();

See the attached snap shot

value for X = 22.73

value for Y= 0.00

Re: Sum function use in table with reference to other table

kushalthakral — Wed, 09 Apr 2014 18:30:34 GMT

Hi Anand

Thanks for ur efforts

but it is giving wrong results

Calculation should be like this

B+C = 199.96

33/199.96= 0.165

and finally 0.165*77.83= 12.84

Re: Sum function use in table with reference to other table

its_anandrjs — Wed, 09 Apr 2014 18:35:14 GMT

Ok i come with update may be formula change

Re: Re: Sum function use in table with reference to other table

maxgro — Wed, 09 Apr 2014 18:44:55 GMT

see attachment

Re: Re: Sum function use in table with reference to other table

its_anandrjs — Wed, 09 Apr 2014 19:34:03 GMT

Hi Kushal,

Now check with new aggregation table

Temp:

LOAD * Inline

[

Model,Import,Export,Total

A, 0.00, 62.78, 62.78

B, 77.83, 35.17, 113.00

C, 0.00, 86.96, 86.96

D, 0.00, 0.90, 0.90

E, 1.39, 0.01, 1.40

F, 122.35, 61.09, 183.44

];

Data:

LOAD

Model,

Sum(Import) as SumImport,

Sum(Export) as SumExport,

Sum(Import) + Sum(Export) as TotalSum

Resident Temp

Group By Model;

DROP Table Temp;

DistinctTable:

load DISTINCT Model as ModelDim Resident Data;

and take a straight table

Dimension:- ModelDim

Expression1 for Import:-

=if(ModelDim='B', (33 / (sum({<Model={'B','C'}>} TotalSum))) * sum({<Model={'B'}>} SumImport),

if(ModelDim='C', (33 / (sum({<Model={'B','C'}>} TotalSum))) * sum({<Model={'C'}>} SumImport)))

Expression2 for Export:- Null()

Expression3 for Total:- Null()

Look at snap shot

Regards

Re: Sum function use in table with reference to other table

kushalthakral — Thu, 10 Apr 2014 05:12:16 GMT

Hi Massimo

Thanks for your Help

Now i m getting the correct values

But still have one concern as the code become very lengthy because on this calculation i have to make summary of it

Regards

Kushal

Re: Sum function use in table with reference to other table

kushalthakral — Thu, 10 Apr 2014 06:56:33 GMT

Also Massimo i am not able to get TOTAL at the bottom.