Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anjali0108
Partner - Creator III
Partner - Creator III

Sort Monthname,year and week number n dimension

Dear Experts,

I have a dimension like this as shown below:

Capture1.JPG

I want to sort this field.

Could anyone help me in this ?

I tried sorting in expression too but not able to get the desired sorting.

Thanks in advance...

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

Hello Anjali,

Hope you are doing well!

You can sort the dimension on the basis of Expression (Chart Properties -> Sort Tab -> Sort by Expression).

For example, you can refer below given sample expression:

Year&MonthNumber&WeekNumber

Hope this will help.

P.S.: If you are still facing issues then please share the application with sample data.

Regards!

Rahul

View solution in original post

10 Replies
sunny_talwar

How is this dimension created? Can you share the script behind this?

rahulpawarb
Specialist III
Specialist III

Hello Anjali,

Hope you are doing well!

You can sort the dimension on the basis of Expression (Chart Properties -> Sort Tab -> Sort by Expression).

For example, you can refer below given sample expression:

Year&MonthNumber&WeekNumber

Hope this will help.

P.S.: If you are still facing issues then please share the application with sample data.

Regards!

Rahul

anjali0108
Partner - Creator III
Partner - Creator III
Author

I created this field using below script:

Common:
load *,
MonthName(CommonDate) & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth;

load *,
if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

LOAD *,
MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

FROM
$(AppQvdPath)Common.qvd
(qvd);

WeekNoAndMonth is the dimension that I am using at last in the chart.

anjali0108
Partner - Creator III
Partner - Creator III
Author

Hi Rahul,

I used the below expression:

num(CommonYear)&num(CommonMonth)&WeekNumberInMonth

and I am getting chart like this:

Capture3.JPG

I am still not able to sort year.

Thanks in advance.

P.S. You can see the script that I used to create this chart in one of my replies above.

sunny_talwar

May be try this:

Common:
LOAD *,
Dual(MonthName(CommonDate) & '- Week' & ' ' & WeekNumberInMonth, (MonthName(CommonDate)*100 + WeekNumberInMonth)) as WeekNoAndMonth;

LOAD *,
if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

LOAD *,
MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

FROM
$(AppQvdPath)Common.qvd
(qvd);

anjali0108
Partner - Creator III
Partner - Creator III
Author

HI Sunny,

The chart is sorted now but the weeks are not forming according to my requirement.

In my case,

week starts from Friday and ends on Thursday.

sasikanth
Master
Master

Try this

From script or exp

Num((Date(CommonDate,'YYYYMM')&WeekNumberInMonth) AS SortNumber

sunny_talwar

This is an issue which came up because of my changes or was this always an issue?

anjali0108
Partner - Creator III
Partner - Creator III
Author

Hi Sunny,

It came because of your changes ,earlier the weeks were coming perfectly fine.