8 Replies Latest reply on Oct 15, 2018 12:23 AM by siva Nageswara Rao

# Plotting/ Creating a static line based on the Axis values

HI Team,

Lets assume  I have some months numbers 1 to 12 for year(All years need not be having 12 months few may have only 5 or 6 etc.).

What I want is like I have to draw a target line based on month number. like if month = 1 then 10 and month =2 then 23 like month=12 then 69.

But it should be static even if you choose a year which is not having 12 months then also the static line should be visible. basically it should not be dependent on any filters.

I tried to get moth numbers on axis plot the target line but when I apply year filter the data getting filter and static line also filtering. which I don't want.

I have fixed the month axis to 1 to 12. i there a way to make calculation based on axis line as i fixed axis static even though data filtered the axis remains same. like that I want my target line to be plotted.

• ###### 1. Re: Plotting/ Creating a static line based on the Axis values

For your problem, why not create a calculated field to derive the month number and return the result, then add this to the graph as a line ( I think you're trying to create something like a bullet chart) to show performance versus target.

Something like this should work:

case DATEPART('month', [your_time_dimension_here])

when 1 then 10

when 2 then 23

when 12 then 69

end

Don't SUM this, make sure it's MIN or MAX.

• ###### 2. Re: Plotting/ Creating a static line based on the Axis values

Thanks Chris McClellan, But if I don't have January month in my date dimension it wont show the target line for month one. Is there a way to make that static.

• ###### 3. Re: Plotting/ Creating a static line based on the Axis values

The fastest/simplest way might be to create a dummy record for January, with a NULL or 0 for the data.

• ###### 4. Re: Plotting/ Creating a static line based on the Axis values

I have attached the a image here.

Here the requirement line the Target line (Black color) should be static even though if you apply filter. For example if you apply year filter 2014 that may not have 60 tenure let say it has only 34 tenure then the target line varies according to the tenure as target calculation based on the tenure.

Is there any way to fix the target line irrespective of tenure changes. I think if we fix the target based on axis values as i fixed axis the target will be fixed.

I am attaching the workbook for your reference.

• ###### 5. Re: Plotting/ Creating a static line based on the Axis values

• ###### 6. Re: Plotting/ Creating a static line based on the Axis values

Chris McClellan, Do you have any idea how to achieve this?

• ###### 7. Re: Plotting/ Creating a static line based on the Axis values

I don't know the answer, but I think I know what's going on.  The Target (copy 2) calculated field is ultimately based on data dimensioned by Cohort Year.

When you filter Cohort Year the underlying data is filtered out of the view and the line is affected.

I'd be guessing (it would take some work to test) if you could have the Target calculation in another datasource that is not associated with year, but if it's based on yearly data you will get the same result in the end anyway

• ###### 8. Re: Plotting/ Creating a static line based on the Axis values

As you suggested I tried to implement the dashboard with two different data source but still there should be column to join the data source but I have again tenure column to join. Even if I applied filters to primary data source eventually that filters the data in secondary data source because we have join based on tenure column.

Please refer the attached workbook for the same scenario.

I have thought but I don't it work or not. Generally  in Data blending we have Primary data source and secondary data source they will joined in way like how left join works. for example If primary data source contains 60 tenures and secondary having 40 tenures if they blended the All 60 tenures will be returned because it's left join. Will it work?

I tried this but I couldn't achieve this , Is my understanding about blending correct?

Thanks,

Siva