3 Replies Latest reply on May 10, 2018 1:22 AM by Divya Bhati

# Trend line for prior year & latest year in single

Hi All,

I need 2 trendlines.

one for prior year monthwise sales

second for latest year monthwise sales.

But the problem is prior/latest year is in integer .

for a prior year I have written calculation : max(year(order date))-1

for a latest year I have written calculation: max(year(order date))

Now to convert this I have again written calculation:

Prior year date:DATEPARSE("dd.mmmm.yyyy",STR([Prior Year])+"/01"+"/01")

latest year date:DATEPARSE("dd.mmmm.yyyy",STR([Latest Year])+"/01"+"/01")

but when i am dragging Prior year date/latest year date to view it is throwing null value.

My main motto is to first calculate prior year & latest year from order date then for latest year /prior year I wanted to draw trendline monthwise in the same view using dual axis.

The second motto is to show the difference between latest year sales & prior year sales and if the difference is higher then 0 then show uu arrow and if the difference is lower then 0 show down arrow but I wanted this arrow in my if condition. So can we place up/down arrow shape in if condition.

for reference I am attaching twbx & screenshot what i want.

• ###### 1. Re: Trend line for prior year & latest year in single

Hi Divya,

The only way I can think of doing this is to calculate your previous years trendlines, then export them (worksheet > export > crosstab to excel), and then re-ingest that trendline into your viz, edit your relationships and build a dual-axis viz.

In this example just to make the trend lines different I've added a random number to the second mark but you can see how this will create the 2 trend lines you are looking for.

HTH 2018.1 attached

Peter

• ###### 2. Re: Trend line for prior year & latest year in single

Hi Peter,

In your solution, it is showing all years I want 2013 (prior year) year sales& 2014(latest year) year sales monthwise.

• ###### 3. Re: Trend line for prior year & latest year in single

I am not able to open your file as it is in the newer version.