topic Only show data with more than 50% variance in QlikView

Only show data with more than 50% variance

Sun, 06 Nov 2011 15:42:23 GMT

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.

Only show data with more than 50% variance

Sun, 06 Nov 2011 18:51:55 GMT

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())

Only show data with more than 50% variance

Mon, 07 Nov 2011 04:19:21 GMT

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

Mon, 07 Nov 2011 04:33:48 GMT

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

Mon, 07 Nov 2011 04:54:11 GMT

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

Re: Only show data with more than 50% variance

Mon, 07 Nov 2011 08:45:08 GMT

Hi,

Please check the attached file.

Hope this will help you.

Only show data with more than 50% variance

Tue, 08 Nov 2011 09:16:03 GMT

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

Tue, 08 Nov 2011 09:35:00 GMT

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.

Only show data with more than 50% variance

SunilChauhan — Tue, 08 Nov 2011 10:00:33 GMT

may be this

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

Only show data with more than 50% variance

Tue, 08 Nov 2011 11:12:21 GMT

Thanks everyone for your help. It resolved my problem