topic Re: Sort opportunities on stacked bar chart in QlikView

Sort opportunities on stacked bar chart

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

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

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 09:08:33 GMT

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

Re: Sort opportunities on stacked bar chart

Anonymous — Thu, 04 May 2017 10:31:41 GMT

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

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 11:20:18 GMT

please post a sample app

Preparing examples for Upload - Reduction and Data Scrambling

Re: Sort opportunities on stacked bar chart

Anonymous — Thu, 04 May 2017 11:31:09 GMT

Please find in attachment an example

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 11:38:00 GMT

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

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 11:38:44 GMT

Re: Sort opportunities on stacked bar chart

Anonymous — Thu, 04 May 2017 11:48:18 GMT

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

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 11:52:45 GMT

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

Re: Sort opportunities on stacked bar chart

Anonymous — Thu, 04 May 2017 11:58:05 GMT

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?

Re: Sort opportunities on stacked bar chart

vinieme12 — Thu, 04 May 2017 12:26:32 GMT

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