topic Re: Sorting issue in date in QlikView

Sorting issue in date

prma7799 — Thu, 26 Nov 2015 11:45:41 GMT

Hi All,

I have taken invoice date and more 5 fields as dimension my requirement is that I want data sorting as by Invoce date.

For that I took = num(month([First Invioce Date])) dimension in first column but data is not sorted as per our requirement.

My expected output is

sorted data should come as apr-14

May-14

aug-14

................

apr 15 like this.

But still am getting below sorting which not correct. Please help

Re: Sorting issue in date

swuehl — Thu, 26 Nov 2015 11:51:01 GMT

Maybe change the sort option for first dimension to numeric ascending?

Re: Sorting issue in date

Anonymous — Thu, 26 Nov 2015 11:52:27 GMT

cud u share some sample data??

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 11:52:48 GMT

Hi Swuehl,

I tried but it will come from 1 2 3 or you can say Jan 15 Feb 15 ...

Thanks

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 11:53:56 GMT

Sorry Balraj I can not share the data.

Re: Sorting issue in date

Anonymous — Thu, 26 Nov 2015 11:56:12 GMT

what is this sort field?

I would say promote your data fields as a First Dimension and then try asc or desc..

Re: Sorting issue in date

swuehl — Thu, 26 Nov 2015 11:58:07 GMT

If you don't want the month numbers, why not just use another QV date function, like

= MonthName([First Invioce Date])

as dimension?

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 11:59:46 GMT

I cant promote because user want first invoice date should there in first column.

Re: Sorting issue in date

Anonymous — Thu, 26 Nov 2015 12:07:50 GMT

Convert your invoice data field into number at script level, then put that field as expression in sort tab(See the attachment)

like:

num(InvoiceDate) as DateNum

then put it as a expression under sort Tab in expression of Invoice Date dimension & sort it asc or desc

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 12:12:00 GMT

As you have seen my expected out put I want sorting data from month Apr....

and for num(InvoiceDate) as DateNum I have already take this in my dimension .

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 12:16:04 GMT

Any help on this

Re: Sorting issue in date

swuehl — Thu, 26 Nov 2015 12:18:56 GMT

The sorting should be correct anyway if you follow previous recommendation.

If you want to create a fiscal year calendar, with year starting in april, have a look at

Fiscal Year

Re: Sorting issue in date

Thu, 26 Nov 2015 12:23:22 GMT

First Make sure [First Invioce Date] field format is strinig or Date .

If it is string then use Date# & Date function to conver string to date then it will wrok .

Num(date(date#([First Invioce Date],'DD/MM/YYYY')))
this expression use in sort tab-> expression

I hope it will work

Re: Sorting issue in date

settu_periasamy — Thu, 26 Nov 2015 12:45:00 GMT

May be try this, in your Sort -> Expression

=Date(Date#([First Invioce Date],'DD/MM/YYYY'))

remove the other sort

Re: Sorting issue in date

prma7799 — Thu, 26 Nov 2015 12:51:27 GMT

Sort is coming from August means 8 5 3 2 like this

August

may

march

feb

I want data should come like below

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Thanks

Re: Sorting issue in date

settu_periasamy — Thu, 26 Nov 2015 13:35:09 GMT

I didn't mean the 'Sort' FieldName(In picture)

Chart Properties -> Sort Tab -> Promote your 'Invoice Data' Field to top. ->

In the Expression

=Date(Date#([First Invioce Date],'DD/MM/YYYY'))

-> Ascending

Re: Sorting issue in date

Fri, 27 Nov 2015 04:39:55 GMT

Hi,

Add these lines in master calendar

Set vFiscalMonthStart = 4 ;

Calendar:
Load Dual(_fiscalYear-1 &'/'& _fiscalYear, _fiscalYear) as FiscalYear,

         Dual(Month, _fiscalMonth)                as FiscalMonth,
          *;
Load Year + If(Month>=$(vFiscalMonthStart ), 1, 0) as _fiscalYear,
         Mod(Month-$(vFiscalMonthStart ), 12)+1        as _fiscalMonth,
          *;

Load * from MasterCalender;

and use FiscalYear&FiscalMonth in sort tab under expression.

Re: Sorting issue in date

elakkians — Fri, 27 Nov 2015 05:44:51 GMT

What you are looking can be achieved by creating fiscal calendar , which has April as the starting month ,

take a look at this

Fiscal and Standard Calendar generation

Re: Sorting issue in date

settu_periasamy — Fri, 27 Nov 2015 07:08:11 GMT

Hi,

Give the same expression which you have used in First Invoice Date:

Chart Properties -> Sort Tab -> Promote your 'Invoice Data' Field to top. ->

In the Expression

=If(date([First Invioce Date])>=Date(vStartDate) and date([First Invioce Date])<=Date(vEndDate),

date([First Invioce Date]))

=> Ascending

Re: Sorting issue in date

buzzy996 — Fri, 27 Nov 2015 08:23:54 GMT

try to sort ur Invoice date in script it self, by using order by invoice date and in sort tab use Load order check box as Original.

hth