Limit date values on continuous x-axis

[#1]

I'm having a problem with a bar/line chart that shows 2 expressions by month.

Since the original document has more than 24 months, i want to limit it in a way that only the last 6 months will be displayed in the chart.

The only way i found to achieve that is to check the option "continuous" in the "Axes" dialogue because then it is possible to define "Static Min/Max" values.

The problem is, if I do so, other date values, that does not exist in my data are being displayed as well and that the bar is somehow cut off strangely (see attached example file).

Does anyone have an idea of how to set that up correctly so that only the last 2 Months are displayed in the chart?

And how can I use the "static step" field if i have dates as a dimension?

Thanks for your help!

Lars

[swuehl]

Lars,

I don't think a chart with a continuous axis is most appropriate here. Try instead to limit your expression results to the months you want to show.

You can use a calculated dimension for that, or by applying a set expression to each aggregation function in your expressions, the set expression should look like:

=Sum(

{<TWELVE_MONTH_ROLLING.NumDate = {">=$(=num(addmonths(max(TWELVE_MONTH_ROLLING.NumDate),-2)))"} >}

[TWELVE_MONTH_ROLLING.Net Sales (consolidated)] )

See also attached,

Stefan

[#3]

Hi Stefan,

thanks for the quick response!

The code you suggested actually does the trick in my example document.

But unfortunately in the real document it doesn't. I suspect that is because there I use the function "accumulate 12 steps back" to build a 12 months rolling diagram.

The problem is that I only want 6 Months in the chart but I need 6 months back plus another 12 months back for the correct calculation of the expression's value.

Any ideas?

[swuehl]

Instead using the chart accumulation property, you can do the accumulation in the expression itself, using advanced aggregation, maybe like

=Sum(

{<TWELVE_MONTH_ROLLING.NumDate = {">=$(=num(addmonths(max(TWELVE_MONTH_ROLLING.NumDate),-2)))"} >}

aggr(rangesum(above(sum({1}[TWELVE_MONTH_ROLLING.Net Sales (consolidated)]),0,12 )),TWELVE_MONTH_ROLLING.MonthYear)

)

See also attached.

Stefan

[#5]

Hi Stefan,

I would like to achieve something similar, but with data broken down in more detail (per item number, customer order lines and dates).

Is it possible to create a rolling week aggregation like you have created above for this data set? Attached you find my file. The top graph represent what I want to achieve, but I only want to see a limited date range in the graph. The grey line is my attempt, but it is clearly not correct.

I hope you can help!

Thanks alot!

Erik

[WeroGreg]

Hi!

New user here, have essentially the same need as the original poster, but using set expressions on each of the measures is not really a feasible approach, as the goal for the graph is for the analyst to be able to swap in and out different measures, so it would be a real hassle to have to apply set expressions every time. Is there really not a way to simply set a max/min on the X-axis in line graphs? 

Thanks!