topic Fields wont sum in script in QlikView

Fields wont sum in script

Mon, 29 Sep 2014 08:09:42 GMT

Any possible solutions as to why two fields wont add in a script?

For example:

Tmp2:

load

Budget1 + Budget2 as BudgetNew

Resident Tmp;

However, if I mulitiply or add to the fields Budget1 and Budget2 it works.

For example:

Tmp3:

load

Budget1 * 2 as BudgetTmp1,

Budget2 + 10 as BudgetTmp2

Resident Tmp;

Regards,

Magen

Re: Fields wont sum in script

m_woolf — Mon, 29 Sep 2014 12:31:47 GMT

Try

Rangesum(Budget1,Budget2) as BudgetNew

Re: Fields wont sum in script

Mon, 29 Sep 2014 13:39:17 GMT

Hi Michael

Thanks for your reponse. Unfortunately it doesnt work.

Please see results below.

Budget1	Budget2	BudgetNew
-	0	0
-	66.01	66.01
-	652.25	652.25
-	841.4	841.4
-	861.55	861.55
-	897.49	897.49
-	917.78	917.78
-	942.41	942.41
-	1076.05	1076.05
-	1080.09	1080.09
429.82	-	429.82
873	-	873
997.56	-	997.56
1046	-	1046
1161.16	-	1161.16
1328	-	1328
1650	-	1650
1666	-	1666
1688.96	-	1688.96

Regards,

Magen

Re: Fields wont sum in script

m_woolf — Mon, 29 Sep 2014 13:42:30 GMT

Are your Budget1 and Budget2 fields in the same table?

Re: Fields wont sum in script

ashfaq_haseeb — Mon, 29 Sep 2014 13:44:18 GMT

Hi,

This means you don't have Granular Data.

I.e there is no logical connection between Budget1 and Budget 2.

You need to look at your data model.

Regards

ASHFAQ

Re: Fields wont sum in script

Mon, 29 Sep 2014 13:59:07 GMT

Try this

Tmp2:

load

ALT(Budget1,0) +ALT( Budget2,0) as BudgetNew

Resident Tmp;

Re: Fields wont sum in script

martinpohl — Mon, 29 Sep 2014 14:11:39 GMT

As I can see you have load datas from source one within the field Budget1 and then datas from a second source within Budget2.

Now you want to calculate these two values but they are not in the same line.

is it possible to join Budget1 and Budget2 over a key field.

e.g.

Temp:

load

profitcenter,

Budget1

from Data1.xls;

left join

load

profitcenter,

Budget2

from Data2.xls;

Facts:

load *,

Budget1+Budget2 as NewBudget

resident Temp;

drop table Temp;

Re: Fields wont sum in script

Mon, 29 Sep 2014 14:20:42 GMT

Are the numbers in the same format?

Your original method did work for me, please see included dashboard!

Kasia

Re: Fields wont sum in script

Mon, 29 Sep 2014 14:21:53 GMT

Yes Michael

Both fields are in the same table.

Regards,

Magen

Re: Fields wont sum in script

m_woolf — Mon, 29 Sep 2014 14:26:56 GMT

Then I think that on any one individual records the are no instances where Budget1 and Budget2 both have values.

Re: Fields wont sum in script

Mon, 29 Sep 2014 14:33:09 GMT

can you then do this:

if(Budget1>0 and Budget2>0, Budget1+Budget2, Budget1) as SumOfBudgets