Only show data with more than 50% variance

[#1]

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%.

Would Appreciate your help.

[#2 · Accepted solution]

Hi

Create a calculated dimension :

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

Where Prod is your dimension.

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

[#3]

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

[#4]

I also tried with aggregated function like below:

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

[#5]

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

[#6]

Hi,

Please check the attached file.

Hope this will help you.

[#7]

Hi

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

Regards

Anu

[#8]

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.

LOAD * INLINE [

    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.

[SunilChauhan]

may be this

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

[#10]

Thanks everyone for your help. It resolved my problem