Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello to All,
My name is Andrea and I am a new QlikView user. I was trying to develop a dashboard about company's index when i found this issue.
I have several firms and each of them belongs to a category. First I have to calc the product of data among column 2 and column 3 for each firm (row) and only then sum the results in order to calc the right value for each category. But the result that i have is this:
Category | Firm | Column1 | Column 2 |
---|---|---|---|
Category 1 | A | 5 | 2 |
Category 1 | B | 6 | 2 |
Category 1 | C | 5 | 2 |
Category 1 | D | 5 | 2 |
Result | 168 |
So first Qlik calc sum of column 1 and sum of column 2 and then it calc the product: 21 x 8 = 168
Instead i would like this result:
Category | Firm | Column 1 | Column 2 | Right Result |
---|---|---|---|---|
Category 1 | A | 5 | 2 | 2.5 |
Category 1 | B | 6 | 2 | 3 |
Category 1 | C | 5 | 2 | 2.5 |
Category 1 | D | 5 | 2 | 2.5 |
Result | 10.5 |
I would like to find a formula that let me do this calc without this problem.
So I would like to ask how can I solve this problem, maybe it is very simple but I have not yet found a solution.
Thanks to All in advanced for any advice.
Can you try this:
Sum([Column1]/[Column 2])
Thanks Sunny
I tried with a simple example and now it's ok.
My question is if there is a general rule in order to avoid this issue even if when i will have to use complex formula.
For Example if I have to calc this field (I used a variable) for each category like the example before:
v_Variable1
sum(
if(
Dilazione<>0,
(($(v_AcquistatoSimulatoCATE)*1.22)/365)*Dilazione,
if(
DilazioneMediaCrediti<>0,
(($(v_AcquistatoSimulatoCATE)*1.22)/365)*DilazioneMediaCrediti,
'n.d.')
)
)
Where "Dilazione" is a number for each firm
Where "v_AcquistatoSimulatoCATE " is
sum(
if(
v_%scontoAquistato=0,
Acquistato+ ((Acquistato*v_VariazioneVolumi)),
(Acquistato+ ((Acquistato*v_VariazioneVolumi)))*(1-v_%scontoAquistato)
)
)
Where "v_%scontoAcquistato is a value insert by me using an input box
Where "Acquistato" is a number for each firm
Where "v_VariazioneVolumi is a value insert by me using another input box
If i try to use a straight table with "Category" as dimension and $(v_Variable1) as expression the result is null.
So i would like to know how to fix this problem and if there are general rules to follow when i want to calc a field in aggregated mode (in this case Category Value) starting from single value (in this case Firms).
Thanks to all in advance