Clarity

Expand all | Collapse all

Data Warehouse Calendar Period Format

  • 1.  Data Warehouse Calendar Period Format

    Posted 02-01-2016 12:42 PM

    New to Jaspersoft - training class pending.

     

    In the meantime, in ad-hoc views, pulling in calendar periods (Quarterly) and noticed that they are presented in QQ-YYYY format.  Also, noticed that when sorting on quarter, they are sorted alphanumerically, not by time/date.  So, when I sort, ascending and expect:

     

    2015-Q4

    2016-Q1

    2016-Q2

    2016-Q3

    2016-Q4

    2017-Q1

     

    Instead, I get

     

    Q1-2016

    Q1-2017

    Q2-2016

    Q3-2016

    Q4-2015

    Q4-2016

     

    Expect the underlying quarter data stored in the datamart is text - not date and that it needs to be changed from QQ-YYYY to YYYY-QQ in order to sort properly.

     

    Any suggestions?  Or, is sorting "calendar data as if it were calendar data instead of text" an Idea that needs submission?



  • 2.  Re: Data Warehouse Calendar Period Format

    Posted 02-18-2016 04:27 PM

    This is still a problem.  Creating views, today, saw this again with Month calendar periods.  For example,

     

    Jan-2015

    Feb-2015

    Mar-2015

    Apr-2015

     

    Sorts/displays as:

     

    Apr-2015

    Feb-2015

    Jan-2015

    Mar-2015

     

    And since Jaspersoft requires date values to be as smaller or smaller than Days AND the DWH smallest calendar value is Weeks, the Time Series charts are not useable, at all.  This is not a total loss, as it seems like the only thing the Time Series charts have vs. Line and Area charts is the ability to move the legend.

     

    Really need to have the calendar values rearranged from Mmm-YYYY format to YYYY-Mmm format.

     

    Is there a workaround that I'm missing?  Can we change the calendar formats ourselves?  Expect we can, worst case, running some SQL Update scripts against the DWH.

     

    At the wall, ready to drink from the waters - anybody out there?  Growing numb, comfortably...wish you were here, feels like I'm on the dark side of the moon....



  • 3.  Re: Data Warehouse Calendar Period Format

    Posted 02-22-2016 08:33 AM

    Dear Roger Waters ,

     

    Is it for Fiscal Period? If so... I had the same issue and got answer from Kathryn on the following post:

     

    AD Hoc Report Time Period Sorted Alphabetically instead of chronlogically

     

    .. Changing  the Fiscal Time Period Name did it for me,

     

    Eric



  • 4.  Re: Data Warehouse Calendar Period Format

    Posted 02-29-2016 09:32 AM

    Hi Eric,

     

    No - not fiscal periods.  Renamed our fiscal periods for same issue, years ago.  This time, it's calendar periods in DWH.

     

    Some workarounds have been identified:

    • include Year as grouping header when using Quarters - this produces the following sort (example):
      • 2015
        • Q1-2015
        • Q2-2015
        • Q3-2015
        • Q4-2015
      • 2016
        • Q1-2016
        • Q2-2016
        • Q3-2016
        • Q4-2016
    • Use Month Start instead of Month, which produces
        • Jan 1, 2015
        • Feb 1, 2015
        • Mar 1, 2015
        • etc....

     

    These are helpful, but still don't fix the problem with using Month which sorts as:

    • Apr-2015
    • Aug-2015
    • Dec-2015
    • Feb-2015
    • Jan-2015
    • Jul-2015
    • Jun-2015
    • Mar-2015
    • etc...

     

    Whatever CA did to sort Month Start, would be good if it were applied to Month.

     

    High hopes and thanks for all the fish,

     

    Dale