topic Re: Set formula operation Error in QlikView

Set formula operation Error

Anonymous — Thu, 19 May 2016 08:08:52 GMT

Hello,

I want to do a difference of two expressions but the result is totally different that i expect:

Here the expression:

2: =SUM({$<source = {'Costs_PC'}>}myVal)+SUM( { $< source = {'FTE_Activity'}>}myVal)/

SUM( TOTAL { 1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)*

SUM( TOTAL { 1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)

3: =(SUM(Value))/1000000 - SUM({$<source = {'Costs_PC'}>}myVal) + SUM( { $< source = {'FTE_Activity'}>}myVal)/

SUM( TOTAL { 1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)*

SUM( TOTAL { 1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)

Do you have a idea please ?

Thanks in advance for your help,

Sébastien.

MindaugasBacius — Thu, 19 May 2016 08:12:20 GMT

=(SUM(Value))/1000000 - SUM({$<source = {'Costs_PC'}>}myVal) - SUM( { $< source = {'FTE_Activity'}>}myVal)/

SUM( TOTAL { 1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)*

SUM( TOTAL { 1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal)

Use this. You missed one minus sign.

UPDATE:

Anonymous — Thu, 19 May 2016 08:22:47 GMT

Thanks a lot, it was exactly that !

Sébastien.

sunny_talwar — Thu, 19 May 2016 10:13:52 GMT

Although this is answered, but just wanted to add that you might benefit from doing a straight subtraction -> A-B to using RangeSum(A, -B)

=RangeSum(

Sum(Value)/1000000,

-(Sum({$<source = {'Costs_PC'}>}myVal) + Sum({$< source = {'FTE_Activity'}>}myVal)/

Sum(TOTAL {1< source = {'FTE_Activity'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal) *

Sum(TOTAL {1< source = {'Allocated_Costs'}, AsOf = {"=$(=GetFieldSelections(AsOf))"} >}myVal))

)