custom sort

[lee_williams]

I'm at a client site and I ONLY have the option to build new dashboards from the web portal in qlikview not the full application (so can't get to the back end code)

I have a field End_Date which looks like a date   01/02/2019

When trying to create a list box of the End_Date or a chart with x - axis being a date I noticed you can't sort it via date, it comes out I think in a load sort, not alphabetical and not date. I have tried to sort the objects a number of ways

as an expression

date(End_Date,'MMM-YY') - didn't work

as a custom sort

Match(month(JOURNEY_END_DATE) & ' ' & year(JOURNEY_END_DATE),
'Jan 19','Feb 19','Mar 19','Apr 19','May 19','Jun 19','Jul 19','Aug 19','Sep 19','Oct 19','Nov 19','Dec 19',
'Jan 20','Feb 20','Mar 20','Apr 20','May 20','Jun 20','Jul 20','Aug 20','Sep 20','Oct 20','Nov 20','Dec 20'
)

But neither gives me the order I require.

 Just to repeat I can only use the web portal so can't fix the data itself

thanks

[sergio0592]

Hi,

Try with :

Match(Date#(month(JOURNEY_END_DATE) & ' ' & year(JOURNEY_END_DATE),'MMM YY')   ,
'Jan 19','Feb 19','Mar 19','Apr 19','May 19','Jun 19','Jul 19','Aug 19','Sep 19','Oct 19','Nov 19','Dec 19',
'Jan 20','Feb 20','Mar 20','Apr 20','May 20','Jun 20','Jul 20','Aug 20','Sep 20','Oct 20','Nov 20','Dec 20'
)

[lee_williams]

No That doesn't work either

[veidlburkhard]

Hi Lee Williams,

looks like your field End_Date is not a date field.

So you first have to interpret this field as a date field: Date#(End_Date,  'DD/MM/YYYY') 

If you enter this definition into the chart properties Sort tab and sort by expression it should work.

Hope this helps

Burkhard

[Kashyap_R · Accepted solution]

Hi

Try giving max(Date) in sort by expression and sort in order that you want

Thanks

[lee_williams]

don't quite understand how

Max(date)     - worked but it does

great  -perfect thanks