Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
kushalthakral
Creator III
Creator III

Sum function use in table with reference to other table

Dear all

i am new to qlikview

i have some requirement like below

i have a table

ModelImportExportTotal
A0.0062.7862.78
B77.8335.17113.00
C0.0086.9686.96
D0.000.900.90
E1.390.011.40
F122.3561.09183.44
201.57246.92448.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

ModelImportExportTotal
A
Bx
Cy
D
E
F

Thanks in advance

Regards

Kushal Thakral

1 Solution

Accepted Solutions
maxgro
MVP
MVP

10 Replies
its_anandrjs

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

maxgro
MVP
MVP

this?

1.png

kushalthakral
Creator III
Creator III

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

its_anandrjs

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

Pivot.png

kushalthakral
Creator III
Creator III

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

its_anandrjs

Ok i come with update may be formula change

maxgro
MVP
MVP

its_anandrjs

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

UpdatePivot.png

Regards

kushalthakral
Creator III
Creator III

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