topic Re: Sorting issue in QlikView

Sorting issue

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

Hi Friends

I have attached my Qv doc where I have created a pivot table showing data relating to Insurance Policies with Premium and claims paid. I want this pivot table to sort where row showing premium should come as 1st row whereas in my pivot table it is last row. Kindly help . My required output table is shown below.

Re: Sorting issue

upaliwije — Tue, 16 Apr 2019 02:19:02 GMT

Can someone pls help me with a solution

Re: Sorting issue

jonathandienst — Tue, 16 Apr 2019 05:03:53 GMT

In a pivot table, the sort order is fixed to the dimension order. The null date will always sort to the bottom of LOSS_DATE, so we need to fix that with a calculated dimension that returns a value that will sort to the top. I used a dual to prevent this bogus value displaying:

=If(Len(LOSS_DATE) = 0, Dual('', MakeDate(1)), Date(LOSS_DATE))

This can then be sorted in ascending order. See the attached qvw file.

Re: Sorting issue

jonathandienst — Tue, 16 Apr 2019 05:10:40 GMT

The calculated dimension could be simpler. This also works:

=If(Len(LOSS_DATE) = 0, Dual('', 0), Date(LOSS_DATE))

or even simpler:
=Alt(Date(LOSS_DATE), Dual('', 0))

Re: Sorting issue

upaliwije — Tue, 16 Apr 2019 09:58:08 GMT

Thanks Jonthy,

When I reload your attachment I get the following output.

Please look into this and help me.

Re: Sorting issue

upaliwije — Wed, 17 Apr 2019 03:07:37 GMT

Dear Jonty,

Could you help me please