Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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))
Please share your sample app to get more and precise solutions/suggestions.
Hi Treseco
Attached qvw as suggested
I want to caluclate the MAC outside the curve, last colun in chart
thanks for any help
Are you talking about the subtotal ? If so please FA, otherwise, can you please describe the expected result?
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))