Date Sort on Chart

[shyamcharan]

Hi Experts,

I have created a chart that has Date Drill object on the X-Axis.

I have sorted out the values by overriding that on the group and used the Date table ID field in expression to sort.

However, this seems not working. Can someone please let me know what is going wrong or how to fix this.

I have attached the QVW that I have created and has issue.

Requirement: When I start drill down, the Years, Month, Month-Week and the date should be orders as per the calender dates.

Thank in advance.

Regards,

Shyam.

[aveeeeeee7en]

Hi

Use this expression in your Sorting:

=if(GetCurrentField(DateDrill)='YEAR_LONG_TEXT',Num(YEAR_LONG_TEXT),

if(GetCurrentField(DateDrill)='MONTH_LONG_NAME',

dual(MONTH_LONG_NAME,Match(MONTH_LONG_NAME,'January','February','March','April','May','June','July','August','September','October','November','December')),

if(GetCurrentField(DateDrill)='WEEK_NUMBER_IN_MONTH_TEXT2',SubField(WEEK_NUMBER_IN_MONTH_TEXT2,' ',2),

if(GetCurrentField(DateDrill)='THE_DATE',purgechar(THE_DATE,'-')))))

Kindly, replace your DrillDown (DateDrill) field (MONTH_SHORT_NAME & '-' & WEEK_NUMBER_IN_MONTH_TEXT2)

with field WEEK_NUMBER_IN_MONTH_TEXT2.

You are drilling Week Numbers on the basis of Months of Months only. Than what's the need of Mar Week 4. Use only Week 4 in place of it.

Make these Settings:

Also, See the Attachment.

Regards

Aviral Nag

[jagan]

Hi,

In script

LOAD *

FROM

DIM_DATE.qvd

(qvd)

ORDER BY SK_DATE_ID;

And now in drilldown group give Sort order Load Order.

Please find the screenshot.

Regards,

Jagan.

[ecolomer · Accepted solution]

Your new file

Is This?

I changed Group type for cyclic

[shyamcharan]

Thank you all. Your tips helped me!

I have got this worked after sorting the month column using the Month number in year expression.

The same with the week number.

It is easy when we have the month number and week number in year columns. I used them in expression to sort.

Thanks again for your help.

[shyamcharan]

Hi Enrique,

Your attached QVW helped me in fixing my issue. As you did, i have sorted the columns using the month number and the week number columns in the dim date table.

However, I still used the Drill Down object.

Thanks heaps.