Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
manpri7078
Creator
Creator

Data for Latest Month available for selected Year

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Like this may be?

View solution in original post

10 Replies
tresesco
MVP
MVP

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

arvind_patil
Partner
Partner

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
Creator
Creator
Author

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
MVP
MVP

Like this?

Capture.PNG

Edit: expression in app updated

manpri7078
Creator
Creator
Author

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
Creator
Creator
Author

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

Regards,

Manish Prasad

tresesco
MVP
MVP

Like this may be?

View solution in original post

manpri7078
Creator
Creator
Author

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
MVP
MVP

This?

Capture.PNG

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