Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, you need to use set analysis. Put this in your expression:
sum( {$<Date = {">=$(=AddMonths(Today()),-10)"} >} Sales )
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
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
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.
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
Make sure you format your date in your load statement like
LOAD
Date(Month_desc,'DD/MM/YYYY') as Month_Desc
FROM...
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
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