Identifying which cyclic group value is being viewed?

[#1]

Hi,

Is it possible to identify which cyclic group expression is currently being displayed?

If so, what is the correct syntax to apply?

------------------------------------------------------------------------------------------------------

ie currently have following formula to sort my bars on a waterfall chart (for when cyclic group = Euros):

sum(Euros)

but would like this to change to sum(QtyKG) when cycled to display volumes, as currently it remains sorted on Euros (as per my definition):

Have tried sorting using descending Y value, Numeric Value etc...  but this then removes the "waterfall" effect (although displays in descending order).

the formula im currently using in the "Bar offset" to create the waterfall effect uses the "above" function:

If(rowno()=1,0,rangesum (above(sum(QtyKG),1,rowno()-1)))

------------------------------------------------------------------------------------------------------

Does anyone know a solution to change the expression in the sort by criteria to reflect the current cyclic group selection?

Any thoughts greatly appreciated,

Kind regards,

Rich

[#2]

Certainly,

I often use

GetCurrentField(c:CycleGroupName)='FieldName'

in the expressions.

Siarhei K.

[#3]

Hi Siarhei,

Many thanks for your response!

I've managed to use the "GetCurrentField(CycleGroupName)" to work, when the group has been applied to a dimension and named.

ie on the attached, can do "GetCurrentField(ReportNew)" and apply in the window title of the chart

Is this approach still achievable on a grouped expression, where no name appears to be defined?

Please advise,

Kind regards,

Rich

[#4]

Sorry, what are you trying to achieve? What is currently the problem with a grouped expression?

[#5]

Within the "sort" tab of the chart, I would like to enter an expression to sort the chart in descending order.

At the moment, I have used "sum(Euros)", and when there was only Euros being displayed in the chart, the waterfall behaves correctly (ie largest value first)

Due to my data, what may be first when sorted by spend, may in fact be 3rd largest when sorted by volume.

In the below image, "category 2" is the 5th largest category by spend (and appears correctly on the chart - when the group is sorted by Euros):

When the cyclical group is selected (bottom left), and displays the QtyKG expression (instead of Euros), "Category 2" clearly shows that it has the largest volume (and should appear as my first entry on the chart) - but this is prevented, because I am sorting by descending spend and not volume in this instance

I was hoping that there was a solution that could identify what "expression" is currently being displayed, and then i can adapt my sort formula accordingly.

ie  if (ChartDisplay = 'Euros',sum(Euros),sum(QtyKG))

where "ChartDisplay" is the magical syntax that I am looking for

Any ideas?

[#6]

Ah, I see.

I've looked everywhere I could, but I can't find a way to determine which expression is currently used, to tweak the sort according to that.

Sorry, hope you find some workaround.

Siarhei K.

[Josh_Good]

Hi,

If you use the "Show Title in Chart" check box on the General tab and leave Title as "<use first expression label>" then the title of the chart will cycle based on the expression.  Hope that helps.

-Josh

[#8]

Hi Josh,

Thanks for your response, although am trying to identify which expression is being displayed in the chart, and then build a formula to sort according to this expression.

ie when the expression is "Euros", then I am sorting the chart in descending order by "Sum(Euros)"

when the expression has been changed to "QtyKG" via the cyclical group, I would like the chart to sort by "Sum(QtyKG)"

Logically, sorting by descending numerical value or y-value could work, but when put in practice, i do not get the desired effects (closest I get, is correct rank of categories, but the bar off-set is in the wrong order - see 3rd image of original post). 

As a result, was wondering whether there was a technique which could identify the reported expression, and then apply this in a formula?

any thoughts, greatly appreciated!

[#9]

what i'm going over right now is reviewing the way you calculate the bar offset.

If i do figure it out, it would be just as simple as setting the sorting of the dimension by Y-value and applying the correct offset.

I'll get back to you, once i figure it out

[johnw]

I barely skimmed the thread, so apologies if I've misunderstood.  So far as I know, you can't check which expression is selected.  The workaround I use is to replace the cyclic expression with a cyclic dimension island, and have the expression choose what to display based on the dimension selected..  See attached, which also includes a different sort order for each expression.