topic Re: sum columns in QlikView

sum columns

Wed, 18 Sep 2013 15:56:21 GMT

Hi all

I have a normal distribution and want to find the values that are outside the Ideal Curve

This is the expression for the Ideal Curve

sum

(count(distinct total MAC)*normdist(Aggr(If( BellCurveTx <= v_US_TX_high and BellCurveTx >=v_US_TX_low, num(BellCurveTx,'#,##0')), BellCurveTx),$(vAVG_TX_Bell_Site),$(vSTD_tx_bell),0))

I get a total at the top of this column

Now I want to find the items that are outside the ideal using this expression

if([# MAC]>[Ideal Curve],[# MAC]-[Ideal Curve],0)

#MAC is Count(distinct MAC) and Ideal Curve expression above

I get a total of 0 for this which is not correct. see attached example

I want to use the totals in a calculation

Any help appreciated

Re: sum columns

Fri, 20 Sep 2013 01:30:06 GMT

Hi Karen,

You'll need to aggregate the "if" in order to get a correct total.

Basically it would look like this (replace with correct expressions):

sum(aggr(if(MacExpression > CurveExpression, MacExpression - CurveExpression, 0), [Tx Pwr])).

You can also attach a sample of your qvw to try with your scenario.

Hope this will help.

Re: sum columns

Fri, 20 Sep 2013 08:32:47 GMT

Hi Jean-Pierre

Thanks for your reply

I have tried your suggesstion and but it does not give me the correct answer, this is my expression.

I probably have brackets in the wrong place. Any help would be appreciated

=sum(
aggr(

(
if(
(count(distinct total MAC)>
sum(count(distinct total MAC)*normdist(Aggr(If( BellCurveTx <= v_US_TX_high and BellCurveTx >=v_US_TX_low, num(BellCurveTx,'#,##0')), BellCurveTx),$(vAVG_TX_Bell_Site),$(vSTD_tx_bell),0))),

(count(distinct total MAC)-
sum(count(distinct total MAC)*normdist(Aggr(If( BellCurveTx <= v_US_TX_high and BellCurveTx >=v_US_TX_low, num(BellCurveTx,'#,##0')), BellCurveTx),$(vAVG_TX_Bell_Site),$(vSTD_tx_bell),0))),0)

)

, BellCurveTx))

Re: sum columns

tresB — Fri, 20 Sep 2013 09:06:30 GMT

Please share your sample app to get more and precise solutions/suggestions.

Re: Re: sum columns

Fri, 20 Sep 2013 10:43:26 GMT

Hi Treseco

Attached qvw as suggested

I want to caluclate the MAC outside the curve, last colun in chart

thanks for any help

Re: Re: Re: sum columns

tresB — Fri, 20 Sep 2013 10:51:15 GMT

Are you talking about the subtotal ? If so please FA, otherwise, can you please describe the expected result?

Re: sum columns

Fri, 20 Sep 2013 10:59:12 GMT

Indeed since you're using a straight table, under the Expressions tab, for the Outside Curve field, you can check the option in the Total Mode as tresesco is suggesting.

But if you were using a pivot table, that expression is also giving a correct total as initially suggested:

sum(aggr(
if(
count(distinct MAC)
>
sum(count(distinct total MAC)*normdist(Aggr(If( BellCurveTx <= v_US_TX_high and BellCurveTx >=v_US_TX_low, num(BellCurveTx,'#,##0')), BellCurveTx),$(vAVG_TX_Bell_Site),$(vSTD_tx_bell),0))
,
count(distinct MAC)
-
sum(count(distinct total MAC)*normdist(Aggr(If( BellCurveTx <= v_US_TX_high and BellCurveTx >=v_US_TX_low, num(BellCurveTx,'#,##0')), BellCurveTx),$(vAVG_TX_Bell_Site),$(vSTD_tx_bell),0))
,
0
)
, BellCurveTx))