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

Sorting issue

Hi,

I have a data set like below.

I want a bar graph and want to limit bars to show only 3 bars with a condition when the gross marging% is higher than 20%.

and secondly i want to sort those bars with an expression - Sum(Revenue).

Gross Margin %(Measurement) - > (Sum(Cost1)+Sum(Cost2)+sum(Cost3))/sum(Revenue)

CustomerName (Dimension) - CustomerName

    

CountryCustomersRevenueCost1Cost2Cost3
IndOrang1004307
RusBharti20005454534
IndRel1001234
RusHP500541135
AmericaOrang234345433335
EMEABharti34237555343
PakRel64576344
BangHP5477343435345
AmericaKelvinator1004543
EMEAwirlpool200068743354
PakOrang1002334
BangBharti5002423223
EMEARel6000464322
MyOrang534543234254
AfganistanBharti45334234
LebanonRel234346
AfganistanHP3454435343111

Regards

Prasanta

23 Replies
sunny_talwar

Do you have sample you can share?

Not applicable
Author

please find attached the qvf file.

In the first bar graph, the sorting order is ok. I just want to show the first two customer (HP and  Orange)

Capture.JPG

swuehl
MVP
MVP

Maybe like this?

2016-04-16 21_07_06-Qlik Sense Desktop.png

Not applicable
Author

can you please share the complete details, how you did it.

Not applicable
Author

if you have used set analysis by entering customer names, then it will not work for me as i have many customers and i cant enter their names in set analysis

amithmurali
Partner - Creator II
Partner - Creator II

Goto Dimensions, there you can see Limitation. It will be in No limitation. select the dropdown arrow and Select exact value or Relative and give the limitation.

For sorting, Goto Sorting, then Drag the Dimension to the Top and uncheck the auto sort and  select sort by expression. then enter the expression.

swuehl
MVP
MVP

The QVF is attached to my previous post, just go through the settings of the charts.

It's mainly a matter of

1) limit the customers using set analysis (but not listing the explicite values, it's a dynamic search based on Revenue rank and margin threshold)

If you want to have the set analysis consider your current customer selection, you can add the intersection operator * to the field modifier (I've done only this change in attached document).

2) Sorting the customers by Sum(Revenue) desc

3) disabling add-on / data handling option 'Show NULL/zero values'

swuehl
MVP
MVP

Amith,

I think the problem is that t is required to consider the sorting when limiting to top3 dimension values.

I couldn't make this work by just using the dimension limit option, since it will look at the expression to limit the values only.

If you could make it work, please post a sample QVF.

Not applicable
Author

this is working. but i am not able to identify, how you limit the bars to 2 in your first bar chart.

But anyway many thanks this set analysis is perfectly working as per my requirement.