topic Identifying which cyclic group value is being viewed? in QlikView

Identifying which cyclic group value is being viewed?

Mon, 08 Aug 2011 18:22:24 GMT

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?

Mon, 08 Aug 2011 18:56:49 GMT

Certainly,

I often use

GetCurrentField(c:CycleGroupName)='FieldName'

in the expressions.

Siarhei K.

Re: Identifying which cyclic group value is being viewed?

Mon, 08 Aug 2011 19:26:49 GMT

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

Identifying which cyclic group value is being viewed?

Mon, 08 Aug 2011 19:42:20 GMT

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?

Mon, 08 Aug 2011 19:59:49 GMT

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?

Mon, 08 Aug 2011 20:24:42 GMT

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?

Josh_Good — Mon, 08 Aug 2011 20:39:43 GMT

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?

Mon, 08 Aug 2011 20:54:12 GMT

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?

Mon, 08 Aug 2011 20:56:47 GMT

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?

johnw — Mon, 08 Aug 2011 22:06:45 GMT

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?

Thu, 11 Aug 2011 16:13:09 GMT

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?

Wed, 20 Jun 2012 01:39:54 GMT

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?

Please help!

Thanks!

Re: Identifying which cyclic group value is being viewed?

Wed, 20 Jun 2012 10:44:25 GMT

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?

Mon, 27 Jul 2015 11:41:44 GMT

Useful Post

Regards,