Dynamic chart comparing previous year month with current year month

[ankurakash]

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

[kaushiknsolanki]

Hi,

Look at the link below.

Set Analysis for certain Point in Time

Regards,

Kaushik Solanki

[ankurakash]

Hi Kaushik,

Thanks for sharing the link.

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

[effinty2112]

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

[#5]

Hope this helps.

[ankurakash]

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

[ankurakash]

Hi Andrew,

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

[#8 · Accepted solution]

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

[ankurakash]

Hi Saugat,

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

Regards,

Ankur