How can i get average of two deviation columns

[Ipsita]

Hi Team,

Below is the data scenarios. Could you please guide me how can i achieve this average deviation.

Brand	RecommendedDate	ActualApplicationDate
Product 1	2/6/2017	12/17/2016
Product 1	4/6/2017	4/1/2017
Product 1	2/28/2017	1/10/2017
Product 1	4/19/2017	1/25/2017
Product 2	2/18/2017	2/19/2017
Product 2	2/21/2017	2/21/2017
Product 2	8/4/2017	1/19/2017
Product 2	4/8/2017	2/1/2017
Product 3	1/18/2017	1/24/2017
Product 3	4/15/2017	4/26/2017
Product 3	4/11/2016	3/8/2018
Product 4	5/9/2017	5/10/2017
Product 4	5/9/2017	5/10/2017
Product 4	5/6/2017	5/11/2017
Product 4	5/6/2017	5/11/2017

Deviation (in days) = Avg. (Actual Application Date – Recommended Date) of each Product

Means, Avg deviation in days of Product 1, Product 2, Product 3 and Product 4.

Thanks You

Ipsita

[arvind_patil]

Hi Ipsita,

use below code :

=(num(Actual Application Date) – num(Recommended Date)) /Count(Product)

Thanks,

Arvind Patil

[sunny_talwar]

Where do you need to calculate this? in a text box object or a chart with Product as dimension?

[Anonymous]

Try these

Option 1: Deviation as a measure

    Move the calculation to the data load script; add ActualApplicationDate - RecommendedDate as Deviation to the script then use the deviation field in charts.

Option 2: Deviation as a dimension

    Add =ActualApplicationDate - RecommendedDate to desired chart

See attached qvf example

[Ipsita]

I need chart with product as dimension.

[Ipsita]

Thanks Arvind for you reply. However, this expression is not working for me.

[Ipsita]

Thanks Ugonna for sharing the qvf. But I have one question here, I am using SQL DB table from data manager so is it possible to load data again from script?? Because as of now data for ActualApplicationDate and RecommendedDate is coming from Data Manger SQL DB loaded tables. Shall I again load the table from script.

[Anonymous]

Hi Ipsita - For the scenario described, you could add the syntax as a preceding load to the script. The updated syntax will look something like this;

Data:

Load *,

ActualApplicationDate - RecommendedDate as Deviation;

Load

Brand,

RecommendedDate,

ActualApplicationDate;

SQL Select

Brand,

RecommendedDate,

ActualApplicationDate

FROM SQL.dbo.tablename;