Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a data set like this below. I need to show the only last 3 months data in a Line Chart. Could someone please help here?
As of now, The Line Chart should have only Oct Nov and Dec data in a Line Chart.
Year | Quarter | Month | Score |
2014 | Q1 | Jan | 81 |
2014 | Q1 | Feb | 72 |
2014 | Q1 | Mar | 85 |
2014 | Q2 | Apr | 89 |
2014 | Q3 | May | 79 |
2014 | Q2 | Jun | 84 |
2014 | Q3 | Jul | 77 |
2014 | Q3 | Aug | 82 |
2014 | Q3 | Sep | 79 |
2014 | Q4 | Oct | 85 |
2014 | Q4 | Nov | 89 |
2014 | Q4 | Dec | 88 |
Hi,
There is one solution for this will be add another field for NUM months in the data model and then use that field in the expression for the SET expression
Raw:
LOAD
Date(Date#(Year,'YYYY'),'YYYY') as Year,
Date(Date#(Month,'MMM'),'MMM') AS Month,
Date(Date#(Month,'MMM'),'MM') AS NumMonth,
Quarter,
Score;
LOAD * INLINE [
Year, Quarter, Month, Score
2014, Q1, Jan, 81
2014, Q1, Feb, 72
2014, Q1, Mar, 85
2014, Q2, Apr, 89
2014, Q3, May, 79
2014, Q2, Jun, 84
2014, Q3, Jul, 77
2014, Q3, Aug, 82
2014, Q3, Sep, 79
2014, Q4, Oct, 85
2014, Q4, Nov, 89
2014, Q4, Dec, 88 ];
Dim1:- Year
Dim2:- Month
Expre:- Sum({< NumMonth ={'>=$(=Num(Month(AddMonths(Today(),-2)))) <=$(=Num(Month(Today())))'}>} Score)
Regards
Anand
If you have actual date values, you can use MonthStart(today(),-2)) to get the last three months.
I wanted to show Oct 2014, Nov 2014 and Dec 2014 on X-Axis of Line Chart
You can still use those as dimensions, what James means is you can use Monthstart or Addmonths functions in your expression to limit your resulting dataset down to the last 3 months.
Hi,
in the load statement put a field with this expression:
If(MonthStart(today(),-2))>MonthStart(yourdate)),1,0) As DataToSubmitFlag
something like that.
Regards
André Gomes
Can you please show an example please- How to use those functions and get the values
Still I am not able to figure it out yet! . Could some one show the example, using the above data please?
Hi,
There is one solution for this will be add another field for NUM months in the data model and then use that field in the expression for the SET expression
Raw:
LOAD
Date(Date#(Year,'YYYY'),'YYYY') as Year,
Date(Date#(Month,'MMM'),'MMM') AS Month,
Date(Date#(Month,'MMM'),'MM') AS NumMonth,
Quarter,
Score;
LOAD * INLINE [
Year, Quarter, Month, Score
2014, Q1, Jan, 81
2014, Q1, Feb, 72
2014, Q1, Mar, 85
2014, Q2, Apr, 89
2014, Q3, May, 79
2014, Q2, Jun, 84
2014, Q3, Jul, 77
2014, Q3, Aug, 82
2014, Q3, Sep, 79
2014, Q4, Oct, 85
2014, Q4, Nov, 89
2014, Q4, Dec, 88 ];
Dim1:- Year
Dim2:- Month
Expre:- Sum({< NumMonth ={'>=$(=Num(Month(AddMonths(Today(),-2)))) <=$(=Num(Month(Today())))'}>} Score)
Regards
Anand
Thank you very much Anand.
I got the concept you explained. It really helped!
Regards!