topic cycle through GROUPS of expressions on a chart........ in QlikView

cycle through GROUPS of expressions on a chart........

Thu, 06 May 2010 14:15:11 GMT

.....is it possible?

Basically, I want to group together two sets of expressions on a chart and then use the cycle button to switch between them. I only have one dimension: order week.

For example:

group 1 = sum of sales; average order value

group 2 = count of orders; count of dispatches

Is it possible to group these expressions as above and then use the cycle button to switch view between them?

Thanks in advance.

cycle through GROUPS of expressions on a chart........

Thu, 06 May 2010 14:36:16 GMT

If there is, I haven't found it. I've had to use variables to create functionality like that by either setting visibility of a chart object or physically changing the expression used in a single chart based on the set value of a variable.

cycle through GROUPS of expressions on a chart........

Thu, 06 May 2010 15:55:43 GMT

Yes you can do this, simply highlight two or more expressions and click on the group button.

If you click on the group button on a single expression it simply adds it with a group to the expression (or group) above.

In terms of macro control etc, you don't have any control over it, but it still functions as a cyclic group of expressions.

cycle through GROUPS of expressions on a chart........

Thu, 06 May 2010 16:17:22 GMT


Nigel West wrote:
Yes you can do this, simply highlight two or more expressions and click on the group button.
If you click on the group button on a single expression it simply adds it with a group to the expression (or group) above.
In terms of macro control etc, you don't have any control over it, but it still functions as a cyclic group of expressions. <div></div>

Thanks for the answer, but it doesn't seem possible to highlight more than one expression..........

cycle through GROUPS of expressions on a chart........

Thu, 06 May 2010 17:05:03 GMT

Sorry, you're right.

The way to do it is to choose the two expressions you want to group together, ensure they are next to each other, then select the second one and click on group. If you then want to add more, just make sure your new expression is immediately below the group, select it, then click on group.

You are then step by step building up a cyclic group of expressions.

cycle through GROUPS of expressions on a chart........

johnw — Thu, 06 May 2010 21:58:03 GMT

But in this case, it sounds like we don't want one group of all the expressions, or even two groups of two expressions each. We want a single group, where selecting the group changes two expressions at once. I'm not thinking of a direct way to do that.

I am thinking of an indirect way to do that. Create new fields, "Group 1" and "Group 2". Make a cyclic group out of them. Write your chart expressions like this:

if(getcurrentfield(MyGroup)='Group 1',sum(Sales),count(Order))
if(getcurrentfield(MyGroup)='Group 1',avg(OrderValue),count(Dispatch))

Probably even easier in version 9, as you could just have the four separate expressions, and show/hide them based on the selected group. Or make the group a list box if you don't need it to have the little circular arrow icon.

Or just make two charts, and show/hide the charts based on the selected group.

This isn't your data or specific to your example, but attached is an example of using a dimension group as a fake expression group. It's the same idea here, just a little more complicated.

Probably other approaches as well.

cycle through GROUPS of expressions on a chart........

Fri, 07 May 2010 09:23:46 GMT


John Witherspoon wrote:
But in this case, it sounds like we don't want one group of all the expressions, or even two groups of two expressions each. We want a single group, where selecting the group changes two expressions at once. I'm not thinking of a direct way to do that.

John, that's exactly what I'm trying to achieve. I'll have a look at the example. Thanks.

cycle through GROUPS of expressions on a chart........

Tue, 18 May 2010 08:59:05 GMT

Thanks everyone for the suggestions.

In the end I used an inline table to create a data island and then used conditional show based on the selection.

I'm sure this is one of many solutions.

cycle through GROUPS of expressions on a chart........

Tue, 05 Oct 2010 15:28:34 GMT

I may just be being stupid, but is there a way to label the cycle button?

cycle through GROUPS of expressions on a chart........

johnw — Tue, 05 Oct 2010 16:32:11 GMT

The example shows the currently-selected expression, if that's what you mean by labeling. If you mean a label like "Please select an expression using the icon below", I'd probably add it with a text box. If you mean show the currently-selected expression for a REAL expression group, I don't know how to do it, which is one reason I tend to use fake expression groups instead.

cycle through GROUPS of expressions on a chart........

Wed, 06 Oct 2010 09:51:09 GMT

Thanks for the example. I had been looking for a label for the REAL expression group - it looked like something that should be there!

It would be a lot easier if there was a function like getcurrentfield() that worked on expressions as well as on dimensions.

cycle through GROUPS of expressions on a chart........

johnw — Wed, 06 Oct 2010 18:18:08 GMT


braikes wrote:I had been looking for a label for the REAL expression group - it looked like something that should be there!

I agree entirely. It SHOULD be there, but isn't. Seems like a mistake.


braikes wrote:It would be a lot easier if there was a function like getcurrentfield() that worked on expressions as well as on dimensions.

That too! In fact, this comes up often enough on the forum that I'm now convinced that it should be a priority for QlikTech (assuming it isn't already in v10). I'm checking the customer portal to see if it's been suggested... Yep, this was suggested less than two weeks ago by ddekov11. I'm promoting it. Feel free to add your vote:

https://emea.salesforce.com/ideas/viewIdea.apexp?id=08720000000HMWx

cycle through GROUPS of expressions on a chart........

Tue, 26 Apr 2011 22:20:46 GMT

Hello,

I have the exact same scenario - can you share more insight into how you accomplished this?

Is the conditional show based on the cycle?

I am trying to do the same thing with a Funnel chart, which may be where the problem is.

Thanks, Maria

cycle through GROUPS of expressions on a chart........

Tue, 26 Apr 2011 22:29:52 GMT

Hi John,

I am also interested in this solution.

I created the 2 fields 'Group1' and 'Group2', and made a cyclic group using those 2 fields. However, the expression does not work. It seem like the IF statement only executes the first sum, it never gets to the second IF statement.

I am trying to do this with a funnel chart by the way, and I am not sure if this will work. I have 3 expressions in the funnel chart and want to be able to cycle thru and display 3 other expressions.

Thanks, Maria

cycle through GROUPS of expressions on a chart........

johnw — Wed, 27 Apr 2011 01:34:37 GMT

I'm not sure what more you want to know. It doesn't look like I used a conditional show in the example. I was just saying that you probably could do it that way instead of how I did it. I did it with an expression for "Value" that depends on the selected field in the group:

pick(match(getcurrentfield(Group),'Revenue','Cost','Profit')
,sum(Revenue)
,sum(Cost)
,sum(Profit))

But it's actually much simpler than that unless I'm missing something (maybe I was trying to give a general template, in case we had very different expressions for each):

sum(Group)

I hid that expression, and then instead showed a dual() expression to get the right label on the field:

dual(getcurrentfield(Group) & ' = ' & Value,Value)

The remaining possibly-confusing part is getting the cycle icon in there. That's actually a bar chart with the group as a dimension and just about everything removed.

I don't know why your if() doesn't work, but you also didn't give me much information to go on. I haven't tried it with a funnel chart, but I don't know why it wouldn't work.

cycle through GROUPS of expressions on a chart........

Wed, 27 Apr 2011 05:05:02 GMT

John,

I think I understand the Pick function, but this code will only execute one expression at a time - sum(revenue), sum(cost) or sum(profit), based on the field selected.

What I would like to do is create 3 expressions for 1 selection, so that if user selects Group1, I would sum up sum(Revenue), sum(Cost). and sum(Profit) all together, in order to display in funnel chart as the 3 separate layers.

Sorry if I am just not seeing this in our example.

Thanks!

cycle through GROUPS of expressions on a chart........

johnw — Wed, 27 Apr 2011 18:38:42 GMT

True, the example doesn't have groups of groups, just groups. I tried to explain how to handle groups of groups earlier, but I didn't give an example. Attached is an example funnel chart using the technique I suggested.