Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Ipsita,
use below code :
=(num(Actual Application Date) – num(Recommended Date)) /Count(Product)
Thanks,
Arvind Patil
Where do you need to calculate this? in a text box object or a chart with Product as dimension?
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
I need chart with product as dimension.
Thanks Arvind for you reply. However, this expression is not working for me.
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.
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;