Solved: Sort opportunities on stacked bar chart - Qlik Community

Anonymous · ‎2017-05-04

Hey all,

I have a problem with 1 of my chart.

The chart here under represents the revenues of all my opportunities by month of the current year.

I have here 2 types of opportunities: Won (colored here in red) and Negociation (in others colors).

I'am trying to regroup all opportunities Won (in red) at the bottom of my chart.

To do so, i have tried the following custom sort in the script:

LOAD * INLINE

[

Stage, Stage order

Award (Closed Won), 1

Closed Lost, 2

Closed No Go, 3

Nego, 4

Proposal, 5

Prospect, 6

];

While i'm trying to regroup (sort) my opportunities_id regarding the Stage.order, i still have some opportunities not at the right place (cf picture above where the yellow is not at the right place and this opportunities is in the Negociation stage).

Any idea to solve this?

Thank you in advance

vinieme12 · ‎2017-05-04

Ok, when i tried i had a selection in the Stage field, that is why it worked. but i will tell you why it is not working.

We are trying to Assign a color based on the Stage

Opportunity.ID can have more than 1 stage so even if we give sort order to it qlikview can't evaluate if it should sort on sort order for Award or Nego !

but the field Opportunity_ID is a one to one relation with the Stage field, so instead of using field Opportunity.ID use Opportunity_ID as a dimension because one ID has only one stage assigned to it and hence only one sort value applies.

Hope this makes sense

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

vinieme12 · ‎2017-05-04

Go to Sort Tab >> Select the Dimensions and >> Select Sort by Expression >> enter [Stage order] >> Ascending or Descending

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-05-04

As you can see, it is what i did:

But i still have this big opportunity in yellow that is not correctly grouped.

I can't figure out why this one, is not working as the others :s

vinieme12 · ‎2017-05-04

please post a sample app

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-05-04

Please find in attachment an example

vinieme12 · ‎2017-05-04

Your Background expression is incomplete!

You added a comma at the end and did not specify <else>

=If(Stage = 'Award (Closed Won)', RGB(155,187,88))

either remove the comma like above or specify the rgbs for the remaining stages and it will work

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

vinieme12 · ‎2017-05-04

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-05-04

Vineeth,

When i modify the background expression i have the same result.

I think that the color attribution is working well but not the sort.

How did you manage to sort like you did in your picture?

Thank you in advance

vinieme12 · ‎2017-05-04

Month field sorted by Numeric >> Ascending

Opp.ID Sortedby Expression >> Stages. Sort Order

i think you also had sort by Text checked for Opp.Id , un-check that and only keep sort by Expression

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-05-04

Dear Vineeth,

I did it as you suggest it but i still have the same result.

Can you transfer the QV you did to obtain this result?

Is it possible that something is wrong in my document settings?