Data for Latest Month available for selected Year

[manpri7078]

Dear All,

I am trying to build expression for displaying data for latest month for the selected year when only year is selected and month is not selected.

And also data for the previous N number of Months. All this for the dimension defined in the straight table chart.

For Example if I select 2017 and the latest data available is for the month of Jun-17 and If I define N number of months to 5, then my output be

1st Expression -  Data for Jun-17

2nd Expression -  Data for May-17

3rd Expression - Data for Apr-17

and so on till Feb-17.

Please guide me for the expressions to be built in.

Regards,

Manish Prasad

[tresesco]

Helping and getting helped on such specific issues is always easier and faster when you provide a sample app. Please share one.

[arvind_patil]

Hi Manish,

vN= Value number of Months Back

I think You need to write three expression

For Current Month:

Sum({<MonthYear={'(=Max(MonthYear))'}>}Sale)

And Check Conditonal enable option in expression where you write If(vN >= 1)

Current Month -1

Sum({<MonthYear={'(=Addmonths(Max(MonthYear)),-1)'}>}Sale)

And Check Conditonal enable option in expression where you write If(vN >= 2)

And Last One:

Sum({<MonthYear={'(=Addmonths(Max(MonthYear)),-2)'}>}Sale)

And Check Conditonal enable option in expression where you write If(vN >= 2)

Thanks,

Arvind Patil

[manpri7078]

Dear Tresesco,

I am attaching sample document for the same.

I am selecting 2018 in the year list box. I am not selecting anything in the Month list box

I have defined dimension as the top 5 customers who have taken maximum orders during 2018.

Now I want the last 5 months data for those customers starting from Jun-17 which is the latest available for the year 2018 till Feb-17 which falls under year 2017 as my financial year starts from Apr every year. So Apr , May and Jun data for 2018 and Feb, Mar data for 2017.

Hope I am clear in my query.

Regards,

Manish Prasad

[tresesco]

Like this?

Edit: expression in app updated

[manpri7078]

Hi,

I am OK with the desired output. Only one part is missing which can make it more dynamic.

I have defined variable for previous N months, say 5.

Now the expressions should be as dynamic as possible, i.e. last 5 months data as defined in the variable.

Is it possible.

Regards

Manish Prasad

[manpri7078]

And also the label names for all expressions to display Month name in the format of MMM-YY.

Regards,

Manish Prasad

[tresesco · Accepted solution]

Like this may be?

[manpri7078]

This is perfect solution. It has really helped me out. The only minor thing. Can we hide the column labeled Months.

If possible then help me out. Otherwise I am more than satisfied with your solution.

Regards,

Manish Prasad

[tresesco]

This?

Remove the black-highlighted 'Month' text from Label.