9 Replies Latest reply: Nov 8, 2011 6:12 AM by anubankar

# Only show data with more than 50% variance

Hi expert,

I have a requirement for conditional reporting. I have Actual and Estimates values and their variance(column 1 - column2) in pivot table. Now I want to only show data where the variance is more than 50%.

• ###### Only show data with more than 50% variance

Hi

Create a calculated dimension :

=if(  aggr( sum(Actual)  ,  Prod)  >1.5 * aggr( sum(Estimate) , Prod) ,Prod , Null())

and check  "suppress when value is nulll

You have to use  aggr if you have multiple values for  each prod item.

If you have only one row for each prod you can of course create  dimension

=if( Actual  >1.5 *Estimate ,Prod , Null())

JJ

• ###### Only show data with more than 50% variance

Hi JJ

Thanks for your reply. I created calculated dimension as you mention but getting "error n calculation" message.

The formula I used is as below:

=if( \$(vActualCost)  > (1.5 * \$(vEstimatedCost)) ,[Invoice No] , Null())

where vActualCost and vEstimatedCost are the variables.

Can you please suggest whats wrong?

Regards

Anu

• ###### Only show data with more than 50% variance

I also tried with aggregated function like below:

=if(aggr(sum(\$(vActualCost)),[Job Code]) > 1.5 * aggr(sum(\$(vEstimatedCost)), [Job Code]) ,[Job Code] , Null())

• ###### Only show data with more than 50% variance

I think the problem is using variable in the formula. Can someone help?

• ###### Re: Only show data with more than 50% variance

Hi,

• ###### Only show data with more than 50% variance

Hi

I am unable to open this QVW file. Can you pl suggest how to open it?

Regards

Anu

• ###### Re: Only show data with more than 50% variance

Hi,

As the attachment file was developed in licensed copy, you wouldnt be able to open it in personal edition.

Below is the script part.

Prod, Actual, Est

A, 234, 100

B, 50, 45

C, 200, 120

];

In the  UI layout,add a straight table as below,

Calculated Dimension : =if( \$(vActualCost)  > (1.5 * \$(vEstimatedCost)) ,Prod , Null())

Expression :

1. Actual

2. Est

Also attached the screenshot of Variable Overview.

• ###### Only show data with more than 50% variance

may be this

if((column (1) - column(2))>.5,(column (1) - column(2)))

• ###### Only show data with more than 50% variance

Thanks everyone for your help. It resolved my problem