Charts to show latest 13 months when entering sheets

[#1]

Hello all,

I'm new to qlikview and I have a requirement that needs the latest 13 months to show in the chart as default when entering the sheet.

So for example it's easy to set the trigger by a specific Year(which will show the latest 12 months) or a specific month, but I want to show the latest 13 months.

Right now I have no trigger set for time period, so when entering the sheet it looks like this:

My wish is when entering the sheet it will by default show the latest 13 months, illustrated below:

What do I need to write in the trigger expression in order to get 13 months, or is it more complicated than that?

Regards

Thomas

[settu_periasamy]

Hi,

May be try with set analysis.. like

Sum({<Month, Year, Date={">=$(=MonthStart(AddMonths(Max({1}Date),-12)))"}>} Measure)

Hope you have the month, year fields..

[settu_periasamy]

You can even use the trigger in active sheet..

Select in Field : Date

Search : ='>='&MonthStart(AddMonths(Max({1}Date),-12))

[#4]

Hi settu,

The trigger solution does not work for me, please see screenshots below.

I've written the code you suggested:

When I enter the sheet it looks like this:

[settu_periasamy]

Hi Thomas,

Can you check your start date format?

In current selection box, it shows 2914-12-01..

Can you show your start date in listbox?

[#6]

Hi settu,

Yes, see image below:

I notice the latest year 2916-01-29?

[#7]

Hello,

I now tried this in the trigger action:

Field: Start_Date

Search String: ='>='&MonthStart(AddMonths(Max({1}Calendar.Month),-12))

Which resulted in the correct months being shown when entering the sheet. However my rolling 12 MMA line is not correct since I'm using Start_Date as field. The only valid fields would be Calendar.Month & Calendar.Year for rolling 12 MMA. And when using any of those fields, incorrect months are shown when entering the sheet.

Not sure how to fix this particular problem.

See image below:

As you can see the yellow line starts at 0 for 2015-02 which is not correct.

Regards

Thomas

[settu_periasamy]

Yes Thomas, Max date shows as 2916-01-29, That's why it doesn't give the data.

When you use  this , ='>='&MonthStart(AddMonths(Max({1}Date),-12))  -> the result would be '2913-01-01'.

I suggest you to, change the date value, Or give the range using less than equal to..

[#9]

Yes, I've done this. However my problem is now that the rolling 12 MMA (the yellow line) does not show correct since I'm using As of Table and the Start_Date does not show that. Only Calendar.Month & Calendar.Year is included in the AsOfTable. So since I'm using Start_Date as my trigger field the rolling 12 /MMA (Yellow line) shows incorrect.

Maybe I'm not explaining in the best way here, but hopefully there's a way to use the Calendar.Month instead of the Start_Date somehow as the trigger field.

/Thomas