Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
see attachment
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
this?
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
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
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
Ok i come with update may be formula change
see attachment
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
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