13 Replies Latest reply: Jul 27, 2015 7:41 AM by Kuldeep Choudhary

# Identifying which cyclic group value is being viewed?

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

• ###### Identifying which cyclic group value is being viewed?

Certainly,

I often use

GetCurrentField(c:CycleGroupName)='FieldName'

in the expressions.

Siarhei K.

• ###### Re: Identifying which cyclic group value is being viewed?

Hi Siarhei,

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?

Kind regards,

Rich

• ###### Identifying which cyclic group value is being viewed?

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

• ###### Re: Identifying which cyclic group value is being viewed?

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?

• ###### Identifying which cyclic group value is being viewed?

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.

• ###### Identifying which cyclic group value is being viewed?

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

• ###### Identifying which cyclic group value is being viewed?

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!

• ###### Identifying which cyclic group value is being viewed?

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

• ###### Re: Identifying which cyclic group value is being viewed?

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.

• ###### Re: Identifying which cyclic group value is being viewed?

Hi John,

Many thanks for your thoughts on the matter!

Managed to include an additional dimension that enables rotation between the two expressions - as per your suggestion, although for some reason, am unable to retain the original "waterfall" look, using the bar offset / above technique.

In the end, have decided to create multiple charts (one for each of the expressions), and then using a drop down listing to select/display the appropriate chart.

Kind regards,

Rich

• ###### Re: Identifying which cyclic group value is being viewed?

Hi,

This might be a bit of a forgotten post, but I'll try nonetheless:

I am trying to create a Waterfall Chart that includes a Total Column to the side in QlikView 11, as shown in RichSheppard 's intial post. I have downloaded the example model, but it doesn't display the same as in the original post. Is that because QlikView 11 is handling the Total column differently? I am using the same expression for my Bar offset and the same expression for my sort order as RichSheppard. I also have a limit of top 10 set in the Dimension Limits section, while also showing 'Others'. Are all of these additional dimension functions screwing with the waterfall chart?

It seems like QlikView is not realising that Total is seperate to the other bars, so it is seeing it as one of the dimensions and offsetting it as well.

Do I need to change the Bar offset expression?

Thanks!

• ###### Re: Identifying which cyclic group value is being viewed?

Hi Hesten,

can you upload an example of your file, and then I'll have a look to see if it displays in v8.5 / see if I can "fix" it...

Kind regards,

Rich

• ###### Re: Identifying which cyclic group value is being viewed?

Useful Post

Regards,

KC