topic Re: Conditional sort in chart in QlikView

Conditional sort in chart

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

Have a chart with a Drilldown group Manager>SalesPerson>Period.

I wish the chart to be sorted Descending on $$ (Sum (Sales)) when Managers and/or SalesPersons are displayed but on Month when Period is displayed.

Tried to add a macro that does the job but that cannot be called from the chart nor can a variable be set (for instance when If(GetSelectedCount(SalesPerson) = 1 a list of months in descending order is the list for the sort)

Doe anyone have a suggestion?

Thanks

Re: Conditional sort in chart

rwunderlich — Wed, 28 Nov 2018 00:39:20 GMT

You can solve this a couple of ways:

Option 1. Edit the Sort Orders (it's a button) in the Group definition. Set the sort for Manager & SalesPerson to sort by expression descending, "sum(Sales)".

Option 2. In your chart on the sort tab, Make your group the primary sort field and check "Override Group Sort Order". Make it sort by expression ascending and use the expression:

=if(GetCurrentField(ManagerDrill)='Period', num(Period), -sum(Sales))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Conditional sort in chart

jduenyas — Wed, 28 Nov 2018 01:18:23 GMT

Sort by Sum(Sales) per Person

Sort By Period (but wrong)

Thank you Rob (Always to the rescue!!)

Did not work as when getting to the Period, it remains sorted on values of the period rather than the period's order (Jan, Feb, Mar...)

Expression is :

=if(GetCurrentField('PEMgrPE')='MonthYear', num(MonthYear), -sum(AGGR(QuotedPrice,QuoteNumber)))

Setting the Sorting in the Drilldown setting resulted in the same.

Second sort is on individual sales person but months are not arranged properly.

Josh

Re: Conditional sort in chart

rwunderlich — Wed, 28 Nov 2018 15:41:46 GMT

=if(GetCurrentField('PEMgrPE')='MonthYear', num(MonthYear), -sum(AGGR(QuotedPrice,QuoteNumber)))

'PeMgrPE' in GetCurrentField() should not be quoted.

sum(AGGR(QuotedPrice,QuoteNumber))) is an interesting looking expression. Why not just Sum(QuotedPrice)?

Can you post a QVW? You can reduce the data and scramble the names. https://community.qlik.com/t5/QlikView-Documents/Preparing-examples-for-Upload-Reduction-and-Data-Scrambling/ta-p/1494262

-Rob

Re: Conditional sort in chart

jduenyas — Wed, 28 Nov 2018 18:04:37 GMT

Thanks Rob

I will look at the link for scrambling data and names and post a QVW (It is very large though)

In essence what I am trying to achieve is a sort on the "Y" (expression) for Managers and Sales People and then switch to sort Alpha (or numeric) on Period not just change the dimension on which the sort occurs.

Without the AGGR the sum returns larger amount because of duplicates. (Probably need to revisit the data structure but as of now it is resolved)

If 'PEMgrPE' is not quoted the expression shows the red squiggly line indicating and error and the top of the expression builder says that there is "Error in expression". With the quotes it does not and at any rate, it works correctly both ways, quoted and not quoted.

I hoped there is a way to switch the sort from Y to Alphanumeric.

Thanks again.

Re: Conditional sort in chart

rwunderlich — Wed, 28 Nov 2018 19:57:41 GMT

You can use the File, Reduce feature to remove most of your data to make your example smaller.

The red squiggle is a bug in the syntax checker. The Group name should not be quoted.

-Rob

Re: Conditional sort in chart

jduenyas — Wed, 28 Nov 2018 23:38:43 GMT

Ok Rob.

Have it ready to attach but how do I attach a QVW?

Josh

Re: Conditional sort in chart

jduenyas — Wed, 28 Nov 2018 23:46:15 GMT

I'll be **bleep**...

It does work Rob.

I removed the Quotes from the Group name and it does work.

As always, you know something more than we do.

Thanks