Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dmohanty
Partner - Specialist
Partner - Specialist

Last 3 Months Data in a Line Chart

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.

                                                                                                      

YearQuarterMonthScore
2014Q1Jan81
2014Q1Feb72
2014Q1Mar85
2014Q2Apr89
2014Q3May79
2014Q2Jun84
2014Q3Jul77
2014Q3Aug82
2014Q3Sep79
2014Q4Oct85
2014Q4Nov89
2014Q4Dec88
1 Solution

Accepted Solutions
its_anandrjs

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)

Last3months.png

Regards

Anand

View solution in original post

8 Replies
ThornOfCrowns
Specialist II
Specialist II

If you have actual date values, you can use MonthStart(today(),-2)) to get the last three months.


dmohanty
Partner - Specialist
Partner - Specialist
Author

I wanted to show Oct 2014,  Nov 2014 and Dec 2014 on X-Axis of Line Chart

Not applicable

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.

agomes1971
Specialist II
Specialist II

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

dmohanty
Partner - Specialist
Partner - Specialist
Author

Can you please show an example please- How to use those functions and get the values

dmohanty
Partner - Specialist
Partner - Specialist
Author

Still I am not able to figure it out yet! . Could some one show the example, using the above data please?

its_anandrjs

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)

Last3months.png

Regards

Anand

dmohanty
Partner - Specialist
Partner - Specialist
Author

Thank you very much Anand.

I got the concept you explained. It really helped!

Regards!