8 Replies Latest reply: Jul 20, 2016 3:58 AM by Ankur Akash

# Dynamic chart comparing previous year month with current year month

Dear Community Members,

I am trying to figure out solution for a dynamic chart that compare previous year month with current year month and as the counting of current year month grows it automatically showcase the compariosn with respective month of last year. Otherwise it should only show the compariosn.

For example:

Current Year = 2016;   Current available Month data = Jan to July

previous Year = 2015;  Previous available Month data = Jan to Dec

The chart (could be bar chart) should display month compariosn between two years for available Months (Jan to July). It should not display previous year months (Aug to Dec) which don't have compariosn months in the current year.

Dynamic behavior required:

If the counting of current year month grows(i.e. Aug and Sep 2016) the chart should include them dynamically and compare it with the respective month of previous year.

A solution or a guiding approach is much appreciated.

Regards,

Ankur

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi,

Set Analysis for certain Point in Time

Regards,

Kaushik Solanki

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Kaushik,

I was trying the following query:

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=MonthStart(Today()))<=\$(=Today())’}>} Sales)

Would it be possible for you to share an example for the shared query.

Regards,

Akash

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Ankur,

Lines something like this in your script:

LET vToday = Today();

if(DayNumberOfYear(Date)<=DayNumberOfYear(\$(vToday)),1,0)  as YTD;

would work well in a date dimension table such as a master calender. Then if you make a year to date comparison you can select YTD = 1 in  a listbox or use the YTD field in a set modifier such as <YTD = {1}>

Cheers

Andrew

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Andrew,

I presume this will give me comparison between date. If the solution is otherwise, I would request more details.

• ###### Re: Dynamic chart comparing previous year month with current year month

Hope this helps.

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Saugat,

I don't have license edition at present. Can you share the same in a text file to me. Appreciate your help.

Regards,

Ankur Akash

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Ankur,

In the setting-->variable overview create the variable:

vvmaxyear=max(year)

vvmaxmonth=max({<year={\$(vvmaxyear)}>}month)

Now,taking year and month as dimensions and in the expressions tab, try this expression.

=sum({<year={"<=\$(vvmaxyear) >=\$(=\$(vvmaxyear)-1)"},month={"=\$(=vvmaxmonth)"}>}sales)

Thanks

• ###### Re: Dynamic chart comparing previous year month with current year month

Hi Saugat,

This works exactly as per my requirement. Thanks for penning the steps and the expressions.

Regards,

Ankur