how to get recent 10 months on trend chart

[#1]

Hello experts,

1. I have to plot a trend chart of sales for RECENT 10 months where as i have total 15 months.

2. my X-axis is xdate (5/1/2013,4/1/2013,3/1/2013.....and so on) and my Y-axis as expression which shows sales

my problem is how will i get recent 10 months out of 15 months? where as i have to ignore oldest 5 months.

Regards,

Santosh

[jvitantonio]

Hi, you need to use set analysis. Put this in your expression:

sum( {$<Date = {">=$(=AddMonths(Today()),-10)"} >} Sales )

[#3]

You can try something like this in the expression in the chart:

sum( {<YearMonth_Sequential={">=$(=max(YearMonth_Sequential)-10)"} >} field)

This will react to the user selections and will always show the most recent 10 YearMonths.

Note: YearMonth is a field containing a sequential number for your YearMonths, with a +1 increment. This way it will work even with the last and first months of the year. You can create this field in the script by doing something like:

LOAD

...

Year * 12 + Month as YearMonth_Sequential,

...

It should look like this in your table (example for 2012 and 2013):

Year	Month	YearMonth_Sequential
2012	1	24145
2012	2	24146
2012	3	24147
2012	4	24148
2012	5	24149
2012	6	24150
2012	7	24151
2012	8	24152
2012	9	24153
2012	10	24154
2012	11	24155
2012	12	24156
2013	1	24157
2013	2	24158
2013	3	24159
2013	4	24160
2013	5	24161
2013	6	24162
2013	7	24163
2013	8	24164
2013	9	24165
2013	10	24166
2013	11	24167
2013	12	24168

Kind Regards,

Borja

[#4]

Hi thanks for your replies, but it is returning me only 10th month.

Here is the field name and the date format which i am using, hope it help you guys better.

Month_Desc

1/1/2012

2/1/2012

3/1/2012

4/1/2012

5/1/2012

6/1/2012

7/1/2012

8/1/2012

9/1/2012

10/1/2012

11/1/2012

12/1/2012

1/1/2013

2/1/2013

3/1/2013

Regards,

Santosh

[#5]

If you are using only that date field, you should go with the solution provided by qlikuser14. Also, check that the values are integer internally and not alphanumeric (they will be numeric if your DateFormat matches that M/D/YYY format).

In order to use my solution you should modify the script and reload the app so you can use the new field with the sequential month number.

[#6]

Hi... thanks for your reply.

Here is the field name and the date format which i am using, hope it help you

Month_Desc

1/1/2012

2/1/2012

3/1/2012

4/1/2012

5/1/2012

6/1/2012

7/1/2012

8/1/2012

9/1/2012

10/1/2012

11/1/2012

12/1/2012

1/1/2013

2/1/2013

3/1/2013

Regards,

Santosh

[jvitantonio]

Make sure you format your date in your load statement like

LOAD

Date(Month_desc,'DD/MM/YYYY') as Month_Desc

FROM...

[#8]

qlikuser14,

i formatted Month_Desc in 'DD/MM/YYYY' format as u suggested but it is returning me all the months. it is not filtering for recent 10 months. here is my expression.

sum( {$<Month_Desc = {">=$(=AddMonths(Month_Desc),-10)"} >} Sales )

Regards,

Santosh

[shree909]

Hi,

i think the month description is in DD/MM/YYYY format whereas the

$(=AddMonths(Month_Desc),-10)"}  is returning the number , so monthdesciption and variable value are not matching.

either u change the monthdescritption to number or

use the below expression

$(=Date(AddMonths(Month_Desc),-10),'DD/MM/YYYY')

sum( {$<Month_Desc = {">=$(=Date(AddMonths(Month_Desc),-10),'DD/MM/YYYY')"} >} Sales )

Thanks