9 Replies Latest reply: Jun 23, 2015 5:20 AM by Birkir Bjornsson

# Dynamic graph from first month of year to selected month via filter

Hi,

I have a small problem. I would like to create a chart like this one. But have the months added dynamically based on what monthnumber the users chooses. So if the user chooses month 4 the chart would display months from 1-4., or like the picture shows month 6 is choosen it display from 1-6. So I guess I need an expression for x-axis, but I cant get my head around.  All hints would be greatly appreciated.

Regards

Birkir

• ###### Re: Dynamic graph from first month of year to selected month via filter

You should be able to do this using set analysis. Add the following to your Expression:

{<MonthNum = {"\$(='>='& Date(YearStart(Max(MonthNum)), 'YYYY-MM') & '<=' & Date(Max(MonthNum), 'YYYY-MM'))"}>}

User will then be able to make a selection in MonthNum list box to determine what he wants to see 2014-03 will display 2014-01, 2014-02 & 2014-03

HTH

Best,

Sunny

• ###### Re: Dynamic graph from first month of year to selected month via filter

You can do this by using set analysis in your expressions.

Please have a look at this introduction:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Basically, you are using something like

=Sum({<Date = {">\$(=Yearstart(max(Date)))<=\$(=max(Date))"}, Month=, Year= >} Sales)

assuming a calendar with Date, Month, Year.

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi I have tried what you suggested but I get a invalid dimension . Here below is a set analysis I use in KPI box, sums up to sales to selected month.

=Sum({\$<MonthNumShort={">=\$(=1)<=\$(=max(MonthNumShort))"}>}Sales)

So could I use this for the dimension members?

{\$<MonthNumShort={">=\$(=1)<=\$(=max(MonthNumShort))"}>}

MonthNumShort is the monthnumber or 1,2,3,4,5,6,7,8,9,10,11,12

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi Birkir,

Dimension:    "MonthNum" or "MonthNumShort" (use only the field you want to display)

Measure:        use set analysis to limit the period you want to display

e.g.

Dimension: MonthNumShort (1-12)

Measure :      Sum(Sales)

-will sum all values according to MonthNum

Sum({\$<MonthNumShort={">=1<=\$(=Max(MonthNumShort))"}>}Sales)

-will show values from 1 to selected month number

Regards,

Ronny

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi Ronny

Thank you for this. This solved this for me. I was trying to solve this my filtering the dimension members on the dimension not by using the measure.

• ###### Re: Dynamic graph from first month of year to selected month via filter

Dear Birkin,

Regards,

Ronny

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi,

I have marked your answear as the correct one

Thanks again

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi,

If you attach sample file then it would be easier to provide solution.

If You have a date field then you can use like below

Sum({<MonthNum=, DateFieldName={{<'>=\$(=YearStart(Max(DateFieldName)))<=\$(=Date(Max(DateFieldName)))'>}}>} Sales)

Replace MonthNum, DateFieldName, Sales with your actual field names.

Regards,

Jagan.

• ###### Re: Dynamic graph from first month of year to selected month via filter

Hi All,

Thank you so much for all of your input, it is an honor to be part of such a community. Ronny came up with a demo that explained this to me and I now get my mistake. I was always trying to filter the dimsion members via Set analysis but not use the measure to filter the members.

Thanks again for all of your support.

Kind regards

Birkir