Sorting issue

[#1]

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

Country	Customers	Revenue	Cost1	Cost2	Cost3
Ind	Orang	100	43	0	7
Rus	Bharti	2000	54	545	34
Ind	Rel	100	12	3	4
Rus	HP	500	54	11	35
America	Orang	2343	454	333	35
EMEA	Bharti	3423	755	534	3
Pak	Rel	645	76	34	4
Bang	HP	5477	343	435	345
America	Kelvinator	100	45	4	3
EMEA	wirlpool	2000	687	43	354
Pak	Orang	100	23	3	4
Bang	Bharti	500	242	32	23
EMEA	Rel	6000	464	32	2
My	Orang	5345	432	342	54
Afganistan	Bharti	453	34	23	4
Lebanon	Rel	234	3	4	6
Afganistan	HP	3454	435	343	111

Regards

Prasanta

[#2]

Any idea, if i can use the same expression for color coding

like,

sum(cost1+cost2+cost3)/sum(Revenue)>.20, red(), green().

but this is not working.

can you please check if the measurement expression you have written can be used in color expression as well

thanks

[#3]

Also, i tried to modify the set analysis by adding to get the data for current year.

But it seems like not working.

can you please check

(sum({<YearCD={$(=max(YearCD))},CustomerName = {"=Rank(Sum(Revenue)*(-1*(Sum(Cost1+Cost2+Cost3)/Sum(Revenue)>=0.2)))<=10"}>} Cost1+Cost2+Cost3) / sum(Revenue))

[swuehl]

The color coding seems to work for me (when using an if() statement and the correct field names), but consider that we are only showing bars with margin >= 20% (hence you will probably see only one color):

=if(sum(Cost1+Cost2+Cost3)/sum(Revenue)>.20, red(), green() )

[swuehl]

Prasanta Kumar wrote:

Also, i tried to modify the set analysis by adding to get the data for current year.

But it seems like not working.

can you please check

(sum({<YearCD={$(=max(YearCD))},CustomerName = {"=Rank(Sum(Revenue)*(-1*(Sum(Cost1+Cost2+Cost3)/Sum(Revenue)>=0.2)))<=10"}>} Cost1+Cost2+Cost3) / sum(Revenue))

I don't see this YearCD field and data in your sample app.

Could you upload another sample with appropriate data and your expected result?

[swuehl]

Prasanta Kumar wrote:

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.

This is the expression for the first chart:

sum(

{<Customers *= {"=Rank(Sum(Revenue))<=3 and (Sum(Cost1+Cost2+Cost3)/Sum(Revenue)>=0.2)"}>} Cost1+Cost2+Cost3)

/ sum(Revenue)

The set expression is filtering Customers that fulfill two conditions (combined by AND operator):

a) Top3  Customer  w.r.t. sum(Revenue)

     Rank(Sum(Revenue))<=3

b) Margin >= 20%

     Sum(Cost1+Cost2+Cost3)/Sum(Revenue)>=0.2

[#7]

Let say, i select a customer from a filter pannel with margin <20%,

so in that case the color should be green . but still its remaining as red.

[swuehl]

Not sure if I understand your issue. Why is this Customer displayed at all (given the filter to only show customers with >= 20% margin)? Which customer are you selecting?

[#9]

Its like, as a dashboard, on bar graph i want to show 10 customers with margin >20% ordered by revenue.

But want to give that flexibility to users to select a customer from filter pannel and see its gross margin in bargraph irrespective of its gross margin value.

when the gross margin is less than 20%, it should become green,

[swuehl]

And you want to do this in one chart?

Maybe use a conditional function to check if the user selected some customers?

If(GetSelectedCount(Customers),

Sum(Cost1+Cost2+Cost3) / Sum(Revenue),

sum({<Customers *= {"=Rank(Sum(Revenue))<=3 and (Sum(Cost1+Cost2+Cost3)/Sum(Revenue)>=0.2)"}>} Cost1+Cost2+Cost3) / sum(Revenue)

)

[#11]

yes, this will be one chart