topic Re: Sort Moth in QlikView

Sort Moth

migueldelval — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have this Dimmension:

=IF( FLAG_LAST_WEEK_MONTH='1', left(MONTH_NUMBER,3)&'-'&(YEAR_NUMBER) ).

But my months aren´t sorted, they are like Picture.

And I need to sort them, in normal order.

Could some body help me?

Regards

Miguel del Valle

Re: Sort Moth

SunilChauhan — Fri, 16 Sep 2016 10:08:56 GMT

in exp of sort tab

num(MONTH_NUMBER)&YEAR_NUMBER

Re: Sort Moth

Anonymous — Fri, 16 Sep 2016 10:11:30 GMT

try in tab sort

using Expression

makedate(year_number, month_number) and sort ascending

what is the Content of your month_number as you take onle left 3 characters?

maybe it is easier if it contains complet date?

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:12:03 GMT

Hi Sunil,

No, I have tested this option and it´s not make anything

Regards

Miguel del Valle

Re: Sort Moth

sunny_talwar — Fri, 16 Sep 2016 10:20:20 GMT

Try this:

=Date(Date#(If( FLAG_LAST_WEEK_MONTH = '1', Left(MONTH_NUMBER,3) & '-' & (YEAR_NUMBER)), 'MMM-YYYY'), 'MMM-YYYY')

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:20:26 GMT

Hi Rudolf,

-Month_Number: January, Fe...(Name field it´s wrong I Know but....)

-YEAR_NUMBER: 2014, 2015 & 2016

Your option it´s not correct too.

If it helps my expression its:

=Sum({<WEEK_FIELD={">=$(=WeekStart(Max(WEEK_FIELD)-365))<=$(=Max(WEEK_FIELD))"}>}CONTACTS)

I need to make this because my WEEK_FIELD it´s really Monday date (16/05/2016). I have diferents charts with diferents times selection.

Regards

Miguel del Valle

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:26:37 GMT

Hi Sunny,

Your answer isn´t making anything

I´ve used my expression in a Sort Expression And it change a little bit but isn´t correct.

Regards

Miguel del Valle

Re: Sort Moth

sunny_talwar — Fri, 16 Sep 2016 10:28:28 GMT

Try using this in place of your calculated dimension

=Date(Date#(If( FLAG_LAST_WEEK_MONTH = '1', Left(MONTH_NUMBER,3) & '-' & (YEAR_NUMBER)), 'MMM-YYYY'), 'MMM-YYYY')

and then sort numerically

Re: Sort Moth

Anonymous — Fri, 16 Sep 2016 10:30:49 GMT

try this in sort field

date#(left(Month_Number,3)&YEAR_NUMBER,'MMMYYYY')

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:39:59 GMT

Hi Sunny T,

Thanks, your are close to solution.

Now Months are sort correctly. BUT I only can see 9 Months. I might to see 13 months.

I am using the next expression.

=Sum({<WEEK_FIELD={">=$(=WeekStart(Max(WEEK_FIELD)-365))<=$(=Max(WEEK_FIELD))"}>}CONTACTS)

Regards

Miguel del Valle

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:42:31 GMT

Hi Rudolph,

Thanks, But your expression it don´t make anything.

Regards

Miguel del Valle

Re: Sort Moth

sunny_talwar — Fri, 16 Sep 2016 10:45:25 GMT

Can you check the environmental variable in the script? See if you have this?

SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

Re: Sort Moth

SunilChauhan — Fri, 16 Sep 2016 10:51:03 GMT

are you getting MonthNUm and YearNum from Date field, then you Can use below in Expression

Date(MonthName(Date),'MMM-YYYY')

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:56:43 GMT

Hi Sunny T,

This it was the problem,I have made change in SET MONTHNAME, and everything it´s correct.

Thank you very much.

I was starting to drive me crazy.

Regards

Miguel del Valle

Re: Sort Moth

sunny_talwar — Fri, 16 Sep 2016 10:58:39 GMT

Awesome, I am glad you were able to figure this out

Best,

Sunny

Re: Sort Moth

migueldelval — Fri, 16 Sep 2016 10:59:12 GMT

Hi Sumil,

Thank you, but it´s better to my qvw Sunny´s answer.

Regards

Miguel del Valle