Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
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
Highlighted

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

Highlighted
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

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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.

Highlighted

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);

Highlighted
Partner
Partner

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.

Highlighted
Specialist III
Specialist III

Try this

From script or exp

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

Highlighted

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

Highlighted
Partner
Partner

Hi Sunny,

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