Rolling 12 months

[Former Member]

Hi all

I'm trying to plot a chart showing latest 12 months (rolling 12 months). At the moment, it looks like this:

I want to select the four months in 2015 + months 5-12 in 2014. I can use the setDataSelection expression to select a series of months in either year or an identical series of months in both years but cannot specify a different number of months in 2014 and 2015 separately.

Is this possible to do within Design Studio?

Thanks and regards,

Antonio

[jeroenvandera]

Hi Antonio,

you can solve it with dataSelection in Script but it is quite complicated. You have to create the selection format in script.

var months = ["01","02","03","04","05","06","07","08","09","10","11","12"];
var keyfigureselect = ', "00O2TRQT6YNCXOG2FRRL0IN5U": ["00O2TRQT6YNCXOG4SEBNIH9TW", "00O2TRQT6YNCXOG4SEBNIH9TW"]}';

var selectedValue = "04.2015";

var Month = selectedValue.substring(0,2);
var Year = selectedValue.substring(3);
var CurrentYear = Convert.stringToInt(Year);
var PreviousYear = CurrentYear - 1;
var MonthRange = "";

months.forEach(function(MonthNumber, index) {
    if (Convert.stringToInt(MonthNumber) > Convert.stringToInt(Month)) {MonthRange = MonthRange + ',"' + PreviousYear + MonthNumber + '"';}
});
months.forEach(function(MonthNumber, index) {
    if (Convert.stringToInt(MonthNumber) <= Convert.stringToInt(Month)) {MonthRange = MonthRange + ',"' + CurrentYear + MonthNumber + '"';}
});


MonthRange = MonthRange.substring(1);
var monthyear = '{"0CALMONTH": [' + MonthRange + ']';

TEXT_1.setText(monthyear + keyfigureselect);
CHART_1.setDataSelection(monthyear + keyfigureselect);

Instead of the hardcoded selectedValue you can use a listbox or dropdown box to pass the selected value.

In my query I had also a keyfigure that is selected in the variable "keyfigureselect"

I got the initial value by using the property in graph to manually select columns and then copy the resulting dataselection string.

I do 2 loops , one for previous year and add all the months greater than the selected month. For current year I take all the ones smaller or equal to.

The TEXT_1 was for debugging purposes 🙂 and finally I pass the new dataselection to the chart.

hope this helps.

best regards,

Jeroen

PS : this was the endresult :

{"0CALMONTH": ["201405","201406","201407","201408","201409","201410","201411","201412","201501","201502","201503","201504"], "00O2TRQT6YNCXOG2FRRL0IN5U": ["00O2TRQT6YNCXOG4SEBNIH9TW", "00O2TRQT6YNCXOG4SEBNIH9TW"]}

[TammyPowlas]

Could you please have a look at this thread? It asks a similar question

[Former Member]

Thanks Tammy.

I saw that but I think it's different in that I don't have a dimension specifically showing latest 12 months of data. All I have in the universe is years and months and I would like to know how I would go about applying a filter on either the data source or the chart to show months May 2014 to April 2015 (if it's possible).

Regards

Antonio

[TammyPowlas]

Antonio - I am not sure about the universe; you might check or to see if it might help. 

[IngoH]

Hello Antonio,

with the Universe you would have to use some of the following options:

- offer the user a start and end month to select and handle those as part of the dashboard

- offer the user a single entry and then handle it in the universe via SQL to filter the right data

- or have a general filter in the Universe based on the system date

regards

Ingo Hilgefort, Visual BI

[Former Member]

Thanks Ingo. Your post got me thinking.

I created 4 prompts in the query asking for Year and Month twice - once for the start month and once for the end month.

Then on start up I passed the current month and year for the second set of prompts and 12 months back for the start month prompts.

Result:

I'm just not sure if this is a good solution performance-wise but seems to do the job.

Regards

Antonio

[IngoH]

Hello Antonio,

it will do the job, but might now be very user friendly.

how about passing just a single day and then doing the rest in the universe ?

regards

Ingo