Skip to Content
avatar image
Former Member

Incorrect sort for Year/Period

Hi,

In my OLAP report I am trying to sort year period field but it is giving wrong output.

Please check below snaps to understand it more.

I had made Year/Period filed Display as key and am trying to sort it in ascending order but unable to sort it. But at backend in BEx I am able to sort it in correct way.

Iโ€™m not sure we can create a variable, in OLAP, to strip some characters and get aresult with only MM.YYYY(001.2013).

Regards,

Mithun

Error Snap.JPG (23.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 13, 2015 at 11:42 PM

    Hi Mithun

    If you create a variable to only get the result MMM.YYYY (eg 001.2013) then you will likely have the same problem... ie the sorted result will be:

    001.2013

    001.2014

    002.2013

    002.2014

    etc.

    When you choose the display option to show "Key" what do you see?

    With AOLAP you can display one thing but sort on the other... and this is often the solution for sorting dates... ie you display the "Text" but when you apply the sort you choose to sort the "Key".

    Regards

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Ian McAlpine

      Hi Ian,

      In analysis office also am facing same issue, please check below screen shot.

      Yes, year/period is a compound characteristic of year and period characteristic.

      If I am sorting period characteristic then also am facing same issue, please check below screen shot.

      In above message I have sent all screen shot with display as key.

      Regards,

      Mithun

      111.JPG (22.9 kB)
      22.JPG (15.8 kB)
  • avatar image
    Former Member
    Jan 29, 2015 at 10:16 AM

    Hi All,

    Any updates on this.

    Regards,

    Mithun.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mithun

      Because both AOLAP and AOFFICE exhibit the same behavior, this indicates a difference of behavior with these tools compared to BEx. Both AOLAP and AOFFICE can sort by text or key so either of these must be modified so that the sort order is correct. For dates the common solution is to adjust the Key so that the sort performs correctly while leaving the Text in a "user friendly" format.

      In Kathy's screenshot notice that she is sorting by Key but is displaying Text... and this has been defined in the query.

      You can contact support to raise an issue why the sort behavior for compound characteristics is different between BEx and the Analysis tools... but a faster solution may be to change the key of the compound characteristics so that the sort order is correct.

      Regards

  • Jan 13, 2015 at 10:18 AM

    Hi Mithun - would you please share what version of BI4 you are using and the SP?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 04, 2015 at 05:35 AM

    Hi Ian,

    As per above Kathy's example I followed same steps but still same behavior.

    Already I am following up with Support team as well and they are suggesting to post issue on SCN forum ๐Ÿ˜”, so what should I do now?

    Regards,

    Mithun.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 08, 2015 at 10:26 AM

    Hello ,

    i have a similar mistake but only for a position bin column . When i try to sort in ascending order , only the firs number is sort .

    Could you help me please ?

    Thank you , Melo


    Capture.PNG (19.7 kB)
    Add comment
    10|10000 characters needed characters exceeded