Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max of one field sorted by other fields in the same Bar Chart

Hi all !

My Data table is looking like this :

CodeCustomer NameCountryTypeSalespersonValue
1Food ShopUSShopJohn300
2Computer ShopSpainShopPaul700
3John DoeUSPersonJohn200
..................

I am trying to build a Bar Shart who shows the max Value of each field :

chart_exemple.png

I tried to find a solution with the script, but it takes more than 10 mins per max and I lost my selection capacity (I need to keep the selections : if I select US, I should see the best salesperson of the US and so on ...).

I tried directly in the chart with the function FirstSortedValue but I fear that I will take too much memory.

Has anyone an idea ? A suggestion ? An example ?

Thanks in advance !

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Benoit,

maybe like attached?

I started just by loading in your original excel table (got rid of the concatenated values),the I used a synthetic dimension and a pick / match to select the appropriate expression.

Regards,

Stefan

View solution in original post

9 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Hi,

Please can you post the sample QVW file? How big is your dataset?

Thanks,

DV

www.QlikShare.com

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

If I understand your requirements correctly you can do this with multiple expressions. I think you can do the bar chart without dimensions, and each expression will do a separate bar.

The expressions for the bars should be something like this:

     Max(Aggr(Sum(Value), [Customer Name]))

     Max(Aggr(Sum(Value), Country))

     Max(Aggr(Sum(Value), Type))

     Max(Aggr(Sum(Value), Salesperson))

To get the bar label, something like:

     FirstSortedValue([Customer Name], -Max(Aggr(Sum(Value), [Customer Name])))

     .. same for Country, etc

Combine that with a copy of the bar expression to build the label.

I am not sure about the X axis labels. You may need to add a "text in chart" object. Oh, and go to the colours tab to set the colours for the 4 expressions to the same colour.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks for answering so quickly !

Deepak -> Unfortunately I can't share a sample (it's confidential data). My dataset has 1 Millions lines per quarter (my goal is to reach 8 quarters of history). However I have built a fake sample and a little application with my current solution.

Jonathan -> I will try to build this and answer you back.

Not applicable
Author

Hi !

I have tried Jonathan's solution. For the bars, it's working without dimension , thx ! As you suggested, I use one expression for each bar.

bar2.png

Unfortunately, the Firstsortedvalue function isn't working. Here his the source code :

=FirstSortedValue(COUNTRY, Max(Aggr(sum({<QUARTER={$(=MAX(QUARTER))}>}VALUE)/1000,COUNTRY)))

At the same time, I am trying to set the text on the top of the bar in order to do something like this :

bar.png

Has anyone any idea ?

Thanks in advance,

Not applicable
Author

Hi !

I tried with this expression :

FirstSortedValue({<QUARTER={$(=MAX(QUARTER))}>}COUNTRY,-VALUE)

But it is still wrong ...

Any idea ?

swuehl
MVP
MVP

Benoit,

maybe like attached?

I started just by loading in your original excel table (got rid of the concatenated values),the I used a synthetic dimension and a pick / match to select the appropriate expression.

Regards,

Stefan

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Firstsortedvalue does not support set expressions.

I suggest that you try:

FirstSortedValue(COUNTRY, -Sum({<QUARTER={"$(=Max(QUARTER))"}>} VALUE))

Hopefully got the brackets right....

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks for your answers,

swuehl -> Your solution is very close from what I am looking for. She has just 2 problems :

- it takes time (~8 sec) to display the bar chart

- I can't show the relative value in the label

Any idea ?

Jonathan ->

Firstsortedvalue does not support set expressions.

Good to know !

swuehl
MVP
MVP

Benoit,

attached is an example with your relative performance numbers in the label.

If you say, it takes time, you probably using the chart with your full data? How big is your table?

We are heavily using advanced aggregation in the chart, which will not perform very well on large data volume.

I haven't got time to look into anything different, so this is all I can offer you today.

Regards,

Stefan