Unable to build trended dashboard chart with date value = Feb 2017,Mar 2017,Apr 2017 etc

0

I have a question about customizing or adjusting the x –axis in Jaspersoft for charts. Currently Jaspersoft defaults to Alphabetic order to list my X-axis labels. I need to adjust this to be based on time so I can show a trend over time. I know the issue has to do with my label names and the fact that Jaspersoft does not understand that the values are suppose to be dates and not text.  (See chart below for issue)

 

Attempts to fix:

Change the data type to a date in sql statement

Creating a calculated field and changing the the data type to date in Jaspersoft.

 

My question,

Is there a way to adjust the axis manually to reflect the intended trend based on time ?

If there isn’t a quick fix, What would the change have to be for Jaspersoft to understand I’m trying to trend based on time. (Starting in 2016-present)

 

Other notes:

1.I've built a domain

2.I'm using sql statement to build a custom table within the domain. The sql looks like this:

Select

Case

when date(a.trandate) between '2015-08-01' and '2015-08-31' then 'Aug 2015'
when date(a.trandate) between '2015-09-01' and '2015-09-30' then 'Sep 2015'
when date(a.trandate) between '2015-10-01' and '2015-10-31' then 'Oct 2015' else null end as Month

 

,Case
when date(a.trandate) between '2015-08-01' and '2015-08-02' then 'Jul 2015 - Week 4'
when date(a.trandate) between '2015-08-03' and '2015-08-09' then 'Aug 2015 - Week 1'
when date(a.trandate) between '2015-08-10' and '2015-08-16' then 'Aug 2015 - Week 2'
when date(a.trandate) between '2015-08-17' and '2015-08-23' then 'Aug 2015 - Week 3'
when date(a.trandate) between '2015-08-24' and '2015-08-30' then 'Aug 2015 - Week 4'
when date(a.trandate) between '2015-08-31' and '2015-09-06' then 'Sep 2015 - Week 1'
when date(a.trandate) between '2015-09-07' and '2015-09-13' then 'Sep 2015 - Week 2'
when date(a.trandate) between '2015-09-14' and '2015-09-20' then 'Sep 2015 - Week 3'
when date(a.trandate) between '2015-09-21' and '2015-09-27' then 'Sep 2015 - Week 4'
when date(a.trandate) between '2015-09-28' and '2015-10-04' then 'Sep 2015 - Week 5'
when date(a.trandate) between '2015-10-05' and '2015-10-11' then 'Oct 2015 - Week 1'
when date(a.trandate) between '2015-10-12' and '2015-10-18' then 'Oct 2015 - Week 2'
when date(a.trandate) between '2015-10-19' and '2015-10-25' then 'Oct 2015 - Week 3'
when date(a.trandate) between '2015-10-26' and '2015-11-01' then 'Oct 2015 - Week 4' else null end as Week

,sales

,GP $

,GP %

 

From table_name

 

Where

etc

,etc

 

Group by

 

Case Statements for Month

Case Statement for Week

 

 

 

 

ehab.anwar's picture
Joined: Sep 14 2017 - 5:37am
Last seen: 1 year 10 months ago

0 Answers:

No answers yet
Feedback