Conditional sort in chart

[jduenyas]

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

[rwunderlich · Accepted solution]

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

[jduenyas]

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

[rwunderlich · Accepted solution]

=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-Sc...

-Rob

[jduenyas]

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.

[rwunderlich]

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

[jduenyas]

Ok Rob.

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

Josh

[jduenyas]

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