Sorting By Date

[OmarBenSalem]

Hi Everyone,

Here is what I'm doing,

I've created an Inline Table :

Timeframe:

LOAD * INLINE [

    TimeframeId, Timeframe

    1, Monthly

    2, Yearly

      ];

So my graphics will vary as I select a Frame,

For example, if I select Monthly, I will see the evolution of my KPI for the last 12 months, yearly is for the last 2 years evolution.

My dimension is as so :

pick(timeframeId,

month(calendar_date)&Year(calendar_date),

year(calendar_date)

.Now, if I select Monthly, I will have this grphic( the evolution for the last 12 months):

My selected Date is 19 of October.

You can see that the sorting is wrong since October2016 should be at the end !

What I haven't understood is, when I select a date in a completely charged month, for example 20 september 2016, I will have a sorted graphic !
I've tried so many expression to counter this and still can't find a way to correct it.

Can anyone help? Thanks !

Omar,

[sunny_talwar]

I think you image isn't attached properly, can you reattach please?

Best,

Sunny

[OmarBenSalem]

[rupamjyotidas]

If the Dates are in NumberFormat. Usually Sorting can be done with this setting.

[Anonymous]

Wouldn't it be easier if you created a cyclic group for your dimension instead of defining an inline table with time frames ?

[sunny_talwar]

Are you using an expression for sorting? Like a Match() function expression? I think you need to use Match(Only({1} FieldName), .....) to not allow for it to drill down based on selections.

[rupamjyotidas · Accepted solution]

Oops I replied too soon. Instead of doing below in Dimension

month(calendar_date)&Year(calendar_date),

Why not in Script you do this

Date(Callendar-Date,'MMM-YYYY') as MonthYear

then you can sort is by the settings above

[OmarBenSalem]

It's the client demand; he want a dynamic dashboard in which all the graphics will change as he select a timeframe

[sunny_talwar]

Or use this as your dimension:

Pick(timeframeId,

MonthName(calendar_date),

Year(calendar_date))

[OmarBenSalem]

That went reaaly good !

I've created a Month-Year field in my calendar; then changed my dimension as so :

pick(timeframeID,

date(MonthYear,'MMM/YYYY'),

year(calendardate)

)

then I sorted by ascending numeric value, and that went good !