topic calculating two different result from different charts in QlikView

calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 10:13:50 GMT

hello comunity,

I have a qvw.file and need a solution for my problem

if you open the file you will see three straight charts.

i want to mulitply the result of gew. Zins =110,13% from straight chart 1 with the result of durchschn. Kosten from straight 3 and call the result of both "general cost"

after i want to subtract the result of Gesamtkosten. zzgl ZV with the result of general cost and call it "New Cost"

can anyone help me out here?

i am a qvw debutant. i know that a solution would be manipulating the script. but actually i am not an expert in set anaylsis. so if anyone could attached me a solution with some explanations, that would be great!

Re: calculating two different result from different charts

Gysbert_Wassenaar — Wed, 17 Jun 2015 12:19:53 GMT

You can't reference values in another chart. You'll have to use the same expressions to calculate that value again. To make things easier you can put expressions in variables. You can then use those variables in expressions. That allows you to reuse expressions.

vInvVolumen: sum([Selling price])

vAnteil: $(vInvVolumen)/sum(total [Selling price])

vGesamtkosten: (1+$(vVerzinsungsberechnung))*$(vInvVolumen)

vVerzinsungsberechnung: ...etc

vGewZins: $(vAnteil)*$(vGesamtkosten)/only([Selling price])

Expression gew.Zins: =$(vGewZins)

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 12:50:35 GMT

"You'll have to use the same expressions to calculate that value again"

I tried to reput the expressions from straight chart 1-3 in a new straight chart. but it does not valclulate the correct value. i dont really know y.

so do I have to put the expressions in variables in the script?

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 12:58:41 GMT

and if i have to set the expression as variables in script, how can i exactly put it in the script. i m sorry for those detailed question, i m still a beginner.

Re: calculating two different result from different charts

Gysbert_Wassenaar — Wed, 17 Jun 2015 13:12:22 GMT

You don't have to create the variables in the script. You can use the Variable Overview from the Settings menu (ctrl+alt+v).

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 13:17:46 GMT

oh ok, Ill try it and let you know if it works!

Re: calculating two different result from different charts

Gysbert_Wassenaar — Wed, 17 Jun 2015 13:23:03 GMT

Your third straight table has only two dimensions. The other two have four. You can add those missing dimensions to your third straight table. Then the expressions should calculate the same values.

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 13:36:16 GMT

if i create a new variable , do i have to to select "Input Contraints" -> "Custom" and add there my expression?

because sometimes when i add the expression as value, it disappears.

Re: calculating two different result from different charts

Gysbert_Wassenaar — Wed, 17 Jun 2015 13:40:34 GMT

No. You don't have to set constraints if you use the Variable Overview dialog from the Settings menu.

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 13:49:23 GMT

"Your third straight table has only two dimensions. The other two have four. You can add those missing dimensions to your third straight table. Then the expressions should calculate the same values."

I attached a qvw file and followed your instructions

if you look at the straight chart 3

[gew.Zins]=110.13%

durchschn. Kosten= 804440 €

i added a new expression called Überzahlung and normally the result should be

Überzahlung=[durchschn. Kosten]*[gew.Zins]= 885929,772

but QV calculates me 12164.17. and thats wrong.

Re: calculating two different result from different charts

thanhphongle — Wed, 17 Jun 2015 14:24:50 GMT

I followed your instruction and tried to set the variables in the setting. it still doesnt work.

i created a new straight table called DURCHSCH. KOSTEN right at the bottom. the result should be 80440,00 EUR but it returns 0. what have i done wrong ?

i attached a new file "reedit2" could you check this out for me please?

Re: calculating two different result from different charts

Gysbert_Wassenaar — Wed, 17 Jun 2015 15:58:42 GMT

You didn't add any dimensions to that table. That's possible, but then you'll have to sum the values over those dimensions: =sum(aggr($(vdurchschnKosten),Kaufschein,[Serial Number],Position,Subposition))

And you need to remove the = sign in front of the expressions in the variables. Otherwise they won't be calculated in the context of the chart where they're used, but on the document level, i.e. without considering any dimensions.

Re: calculating two different result from different charts

thanhphongle — Fri, 19 Jun 2015 08:51:06 GMT

hmm still doesnt work, could you attach me a qvw file with your solution?

Re: calculating two different result from different charts

Gysbert_Wassenaar — Fri, 19 Jun 2015 09:01:23 GMT

See attached qvw

Re: calculating two different result from different charts

thanhphongle — Fri, 19 Jun 2015 10:07:31 GMT

I attached a new qv file.

I created my variables by following your instructions

If I try to return vGewZins in an expression, so the expression should be like this?

=sum(aggr($(vGewZins),Kaufschein,[Serial Number],Position,Subposition))

somehow it doesnt return me 106,42%

I m not sure where mistake is again.

Moreover if i want to mulitply vPVKosten with vGew.Zins in an expression, so it must be like this? ->

(sum(aggr($(vGewZins),Kaufschein,[Serial Number],Position,Subposition))) * (sum(aggr($(vPVKosten),Kaufschein,[Serial Number],Position,Subposition)))

could u attach me your solution again, I m very thankful that you help me out here.