8 Replies Latest reply: May 28, 2013 11:21 AM by Sree Anarasi

# how to get recent 10 months on trend chart

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

• ###### Re: how to get recent 10 months on trend chart

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

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

• ###### Re: how to get recent 10 months on trend chart

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

• ###### Re: how to get recent 10 months on trend chart

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:

...

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

• ###### Re: how to get recent 10 months on trend chart

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

• ###### Re: how to get recent 10 months on trend chart

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.

• ###### Re: how to get recent 10 months on trend chart

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

FROM...

• ###### Re: how to get recent 10 months on trend chart

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

• ###### Re: how to get recent 10 months on trend chart

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