topic Re: How to get the addition in third column in QlikView

How to get the addition in third column

abc_18 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

in attached image I have column a and b

column a expression is =Max({<CSMmilestonename={'Build'}>}SprintNo)

and column b expression is =Min({<CSMmilestonename=-{'Build'}>}CSMsprints1)

in third column i need to get the addition of these two

that is 54+1+6+1, '54' from column a

and '1+6+1' from b.

please help

Re: How to get the addition in third column

antoniotiman — Tue, 04 Jul 2017 10:49:41 GMT

Try

Alt(a,0)+Alt(b,0)

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 10:54:03 GMT

using this formula I am getting column 'a+b' data, but my b column data should come from 1 and result should be

'54+1+6+1'

54 is for Build from column a

and 1+6+1 is for '=-Build' from column b

Re: How to get the addition in third column

MindaugasBacius — Tue, 04 Jul 2017 10:57:02 GMT

Try like this:

=Sum(Aggr(Max({<CSMmilestonename={'Build'}>}SprintNo), CSMmilestonename))

=Sum(Aggr(Min({<CSMmilestonename=-{'Build'}>}CSMsprints1), CSMmilestonename))

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:01:19 GMT

For column a and b by using above expression as mentioned in my original post I am getting correct .

how to get addition in third column that is one level down.

output should be 54+1+6+1=62

Re: How to get the addition in third column

antoniotiman — Tue, 04 Jul 2017 11:05:28 GMT

RangeSum(Top(Sum(a+b),1,NoOfRows()))

or provide sample data

Re: How to get the addition in third column

Anonymous — Tue, 04 Jul 2017 11:13:29 GMT

rangesum(a,b)

Re: How to get the addition in third column

Anonymous — Tue, 04 Jul 2017 11:14:58 GMT

Hi Shikha,

The problem here is due to nulls. If you add null to any value, the result will be a null.

So use something like this

(IF(isnull([Column a]),0,[Column a])+

IF(isnull([Column b]),0,[Column b])

) as Column C

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:21:01 GMT

Hi Antonio,

Not working using Rangesum function.

Attaching sample file, Please check

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:24:35 GMT

Hi Fayez,

Not getting proper output ,attaching sample data.

Output should be sum of both column

Re: How to get the addition in third column

Anonymous — Tue, 04 Jul 2017 11:27:30 GMT

Now I see it,

a and b are dimensions...

So you'll need a synthetic dimension (valuelist()) or precalculate it in script.

Please prepare a qvw sample.

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:29:58 GMT

Hi Robin,

column a and b are expressions.

Thanks

Re: How to get the addition in third column

Anonymous — Tue, 04 Jul 2017 11:31:34 GMT

please prepare a sample...

Re: How to get the addition in third column

antoniotiman — Tue, 04 Jul 2017 11:31:59 GMT

I am sorry, I need Your App.

However try

Sum(Aggr(Sum(Alt(a,0)+Alt(b,0)),[Capability Name]))

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:37:28 GMT

Attaching the Qvw sample, here column 1 and column 2 are dimensions, but in original post these are expressions.

Re: How to get the addition in third column

Anonymous — Tue, 04 Jul 2017 11:37:38 GMT

It is working for me.

Try putting

Set NullInterpret = '';

in your script

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:40:53 GMT

Fayez you should get sum as 62

sum(a)+sum(b) should be 62 for capabilty name

Re: How to get the addition in third column

tyagishaila — Tue, 04 Jul 2017 11:51:26 GMT

Sum(Total Aggr(Sum(a)+Sum(b),CapabilityName))

Re: How to get the addition in third column

abc_18 — Tue, 04 Jul 2017 11:54:42 GMT

Getting '0' values in that column.

Re: How to get the addition in third column

tyagishaila — Tue, 04 Jul 2017 12:22:37 GMT

Create variable for a and b values

and then try it

Sum(Total Aggr($(vA)+$(vB) ,C_Name))