Rank function with Cycle Group of expression

[#1]

I have a cycle group of few dimensions in my chart as my first column. Second column is a group of expressions. And third, column is a rank expression.

Group of expressions are for example, sum(Pen), sum(Pencil), sum(marker).

Rank expressions for the individual will be rank(sum(Pen), 1) rank(sum(Pencil), 1) and rank(sum(marker), 1)

User have a privilege to select any dimension from the first column and any sum from the second column, and my rank should be populated based on the two selections just made.

Is it possible to update the rank based on the group of expressions? When user switches from pen to pencil my rank should be updated automatically and so on so forth.

I hope above explanation is clear enough.

[johnw · Accepted solution]

I don't think you can detect which expression is selected from a group, so I don't think you can synchronize the sum() and rank() expressions directly.

As an indirect way to do it, you could create a cyclic field group with Pen, Pencil and Marker. Create some useless object, like a chart with no data, that uses this field group. You're just trying to get the field name and the little cycle icon. Stick that on top of the sum() column label. Then use expresions like this:

pick(match(getcurrentfield(MyGroup),'Pen','Pencil','Marker')
,sum(Pen),sum(Pencil),sum(Marker))
pick(match(getcurrentfield(MyGroup),'Pen','Pencil','Marker')
,rank(sum(Pen),1),rank(sum(Pencil),1),rank(sum(Marker),1))

Untested, but I think it would work.

[#3]

It works for me. There were some complications for example, if I minimize or move the bottom chart, the artificial cycle group won't move with it. I ended up using a macro for that. Anyways, is it possible to have ' - ' for every sum(pen) or sum(pencil), sum(marker) = 0.

For example, when the sum(pen) = 0, I want my rank to display ' - ' instead of giving it any rank. No rank should be given to any sum where value is '0'. Do I have to modify my expression for this? or do I set some property on the chart?

Thanks for your help John.

[johnw]

Probably this:

if(column(1),YourCurrentExpressionForTheRankColumn)

This basically says if the first column (the one with the sum) is 0, then use null() for the rank, otherwise calculate the rank.

[#5]

It worked. Thanks John.