topic Re: Date Sorting issue in QlikView

Date Sorting issue

ahmar811 — Wed, 01 Jun 2016 04:36:18 GMT

experts,

i have date field in MMM YY format.

i want to sort my bar chart to the specific order like below.

MTD MTD-1 MTD-2 [last Year MTD]

June 2016 May 2016 Apr 2016 June 2015

please find attached sample file for your reference.

Thanks & Regards

Ahmar Ansari

Re: Date Sorting issue

prasadcm — Wed, 01 Jun 2016 05:12:25 GMT

Try to just check the Load Order->Reversed in Sort Tab,uncheck every other option in sort tab.

Re: Date Sorting issue

marcus_sommer — Wed, 01 Jun 2016 05:17:27 GMT

Quite often worked a sorting-expression like: avg(Date) for it (if those yearmonth-field is related to a date).

- Marcus

Re: Date Sorting issue

qlikview979 — Wed, 01 Jun 2016 05:22:34 GMT

please find the attached file

Try like this

Write this in sort tab

if(FMonthYear2='Apr 17',1,if(FMonthYear2='Mar 16',2,if(FMonthYear2='Feb 16',3,if(FMonthYear2='Apr 16',4))))

Regards,

Mahesh

Re: Date Sorting issue

Wed, 01 Jun 2016 05:41:49 GMT

Hi Ahmar,

In sort bt expression use below expression:

=MATCH(MAX(mAKEDATE(FiscalYear,FiscalMonth)),MAX(MAKEDATE(FiscalYear,FiscalMonth))-1,MAX(MAKEDATE(FiscalYear,FiscalMonth))-2)

Find attached app. Its working as per your sort requirement.

Re: Date Sorting issue

sunny_talwar — Wed, 01 Jun 2016 10:50:10 GMT

How about this?

Sum({<FMonthYear2 = {"$(='>=' & Date(MonthStart(Max(FMonthYear2), 0), 'MMM YY') & '<=' & Date(MonthEnd(Max(FMonthYear2), 0), 'MMM YY'))",

"$(='>=' & Date(MonthStart(Max(FMonthYear2), -1), 'MMM YY') & '<=' & Date(MonthEnd(Max(FMonthYear2), -1), 'MMM YY'))",

"$(='>=' & Date(MonthStart(Max(FMonthYear2), -2), 'MMM YY') & '<=' & Date(MonthEnd(Max(FMonthYear2), -2), 'MMM YY'))",

"$(='>=' & Date(AddYears(MonthStart(Max(FMonthYear2), 0), -1), 'MMM YY') & '<=' & Date(AddYears(MonthEnd(Max(FMonthYear2), 0), -1), 'MMM YY'))"}

,FiscalMonth, FiscalYear>}Value)

Re: Date Sorting issue

Fri, 03 Jun 2016 07:52:11 GMT

Just another Approach...

why not switching the format from MMMM YY to YY/MMMM ?

(e.g. by right(DateField,2)&'/'&left(Datefield,4), or if you have the date you can insert it in the Date() function).

it will sort your Charts automatically without too sophisticated formulas...

Re: Date Sorting issue

ahmar811 — Fri, 10 Jun 2016 04:11:14 GMT

Thank you friends for your support.i just solved the issue from load script by using order by.

Thanks & Regards