How to create a moving reference line for “today”?

by Nov 4, 2015

Recently I came across these following questions,
A) I have a data set with date field, most recent event captured in this data set is about 3 months ago. I want to analyze progress from start date till Today, even if data is not available for past few days.

B) Being an online seller, considering sales of past few months and future orders in hand; I want to differentiate between trends of profit earned and estimated profit.

How can I achieve such use cases in VA?


swapnil chaudhari over 7 years ago
We have introduced a new feature in ADS 17.0 called “Reference lines” which will help you to achieve the desired behavior using Visual Analytics.
Below are step by step instructions to achieve above usecases with moving reference line for “Today”.
I’ve attached a sample vizx file as well.

1: Create a desired chart in Visual Analytics (VA), using date and measure field from the dataset.
2: Create a Calculated Field called “Today” with the formula: TODAY()
3: Drag the “Today” field to the Break deck & convert it to Exact Date – Continuous
4: On your date axis right click & choose “Add Reference Line…”
5: In The “Add Reference Line” UI, choose the following options:
a) “Line” (already selected by default)
b) “Scope: Per Pane” (already selected by default)
c) Line – Value: From the dropdown, choose Break deck field “Today”
Line – Label: Select Option “None”
d) Formatting – Line: This is optional. I’ve chosen to make it a dashed line w/ red color.
6: Click “OK”.

Now, This will create a reference line in the chart on Today’s date.

Usecase A)
1: If in your Column deck’s date field, data has not been captured for past few days, then time gap is displayed in the chart between most current date in dataset and Today’s date.
2: The reference line present on Today’s date is moving reference line, because it’s position will be changing with each changing day.
Please refer attached UseCase_A.png and ReferenceLine_MovingToday.vizx (Worksheet: “No Sales Since OctoberEnd”)

Usecase B)
1: If Column’s deck date field has future values also, then reference line will be displayed on today’s date separating past and future measure values.
2: This chart can be improved visually by adding different colors for past and future values.
To achieve this, use following steps:
a) Create a calculated field called “Profit Analysis” with formula: IF [OrderDate] > [Today] THEN “Estimated Profit” ELSE “Profit Earned”
b) Use this field in colors deck
c) Use required colors, by editing color pallet.

Now, Past and Future values are displayed using two distinct colors.
You can easily distinguish between trends of profit earned till now and estimated future profit.
Please refer attached UseCase_B.png and ReferenceLine_MovingToday.vizx (Worksheet: “Profit Analysis”)

(Please Zoom-In the images and use horizontal scroll for better view )