topic Need help with dynamic expressions in a chart in QlikView

Need help with dynamic expressions in a chart

diffeyj — Wed, 05 Jan 2011 06:09:17 GMT

Hi All,

I have a chart (straight table) that I want to display columns accross the page, for the amount of months. I think the best way to do this is with an example.

If I select the months Jan, Feb and Mar for 2011, this is what I want to be able to do.

So the more months I select the more expressions I require. Is there away of doing this dynamically. I don't want to limit the amount of columns available, eventually there will just be a scroll bar. If I was to limit the amount of columns I could just do a lot of expressions visible on condition. But I don't think this will work for me, as I want the ability to select all months sometimes (so 24, 36, etc. months)

Any assistance would be greatly appreciated.

Regards

Josh

Need help with dynamic expressions in a chart

matt_crowther — Wed, 05 Jan 2011 10:26:02 GMT

Josh,

From what you've described above the most suitable method would be to use a pivot table:

Customer Name as First Dimension, Month as the Second (horizontal across the top) and then your Sales, Costs & Margin calc as 3 expressions, finally using sub totals you'll get your totals.

When selections are made within the Month dimension this will alter the months returned in the chart.

Hope that helps,

Matt - Visual Analytics Ltd

Need help with dynamic expressions in a chart

diffeyj — Wed, 05 Jan 2011 23:19:46 GMT

Hi Matt,

Thanx for the response, but I am not sure if that will get the result I am after.

The desired result is something like this (done manually in excel)

The result I get from setting the dimensions to be horizontal

Notice that this puts the customer accross the page also. Basically the issue here is that a client of mine is replacing another product with QlikView (yay!) however there are somethings that their previous product did they said they 'must' have due to user acceptance.

So if anyone can assist with this one that would be great.

Regards

Josh

Need help with dynamic expressions in a chart

johnw — Thu, 06 Jan 2011 00:37:24 GMT

You can move the pivot table dimensions around. Move the customer to the left and leave the period on top. You'll need three expressions - Sales, Costs and Margin. For the total, go to properties, presentation tab, click on the Period dimension, and checkmark "show partial sums". You might need to click on "subtotals on top" to get the totals in the right place. Once you have everything looking like you want it, you should probably uncheck "allow pivoting" and check "always fully expanded".

Need help with dynamic expressions in a chart

diffeyj — Thu, 06 Jan 2011 00:57:10 GMT

Hi John,

Thanx for the reply. I have had a look at the dimensions tab and the presentation tab, and there is nothing on there that seems to let me move one dimension to be horizontal and one to stay as vertical. I'm clearly missing something here

regards, josh

Need help with dynamic expressions in a chart

johnw — Thu, 06 Jan 2011 01:04:07 GMT

Drag and drop. As you're dragging, a blue arrow will appear that indicates where the dimension you're dragging would appear if you drop it. Even users can do that unless you specifically disallow it by setting the properties as I described.

Need help with dynamic expressions in a chart

diffeyj — Thu, 06 Jan 2011 01:20:07 GMT

wow... I did not realise that... 🙂

Ok. so it looks like I am almost there. The issue I have with switching it to a pivot table is it appears that I can't make the expressions or dimensions show on a condition. As a straight table I had some buttons that the users would press to show additional expressions, or dimension columns (they were visible on a variable which the buttons updated).

Am I stuck with you can see everything?

cheers

josh

Need help with dynamic expressions in a chart

johnw — Thu, 06 Jan 2011 01:31:23 GMT

Hmmm, so you were using a conditional show on the presentation tab in your straight table? I wish they'd support that on pivot tables; it's one of those random inconsistencies in behavior that'll drive you nuts.

The only way I remember how to do it in a pivot table is with macros. A macro can enable and disable the expressions as desired, making them appear and disappear.

The attached is probably WAY more complicated than what you need, but it includes code to enable and disable expressions based on user selections. It's a straight table, but appears to still work fine if you change the chart to a pivot table. I believe if you just borrowed the expression logic, the dimensions wouldn't collapse each time like they do. I think they're doing it in my example because I think I remove all dimensions and then add them all again, so it can't store any state information. As long as you just fiddle with the expressions, I think it would store the state information and just "show and hide" the expressions cleanly.

Need help with dynamic expressions in a chart

diffeyj — Thu, 06 Jan 2011 02:15:16 GMT

Hi John,

That worked a treat. As you said, it was a bit over the top for what I wanted, however got the jist of it and wrote some functions and I'm calling them from the buttons. It all seems to work as expected now.

Thank you

Josh

Need help with dynamic expressions in a chart

diffeyj — Thu, 06 Jan 2011 02:52:34 GMT

hi John,

are you aware of an api for all the qlikview objects in VB? So for example I want to see what functions exist that I could use, as well as what attributes various objects have. Your example has pointed me in the right direction, but when it comes to the dimensions, I want to see what options I have available to me.

Regards

Josh

Need help with dynamic expressions in a chart

johnw — Thu, 06 Jan 2011 19:12:00 GMT

There should be an APIguide.qvw in your QlikView\Documentation directory that covers all of that.

Need help with dynamic expressions in a chart

diffeyj — Fri, 07 Jan 2011 03:59:54 GMT

Hi John,

thanx for all the help so far. I have had a look at the APIguide.qvw, and it is not the easiest API I have ever used... I have spent a while looking for what I am after and have come up empty. Basically, going with similar to what we have been discussing I want to be able to add dimensions via a button as well as the expressions. The expressions have worked a treat, and I am happy with how that works. The dimensions on the other hand are being a little tricky.

I have worked out how to remove dimensions I don't want to see anymore by having just a little function that goes through the dimensions, checks the PseudoDef.Name and then removes it if it finds it. The issue is adding it if it isn't already there. When I add a dimension, it adds it, however, because it just adds it to the list of dimensions, it is adding it with it going horizontal. I cannot work out a way within the macro of switching it to be vertical.

any ideas?

cheers, josh

Need help with dynamic expressions in a chart

diffeyj — Fri, 07 Jan 2011 04:01:16 GMT

I think I need to promote the dimension so it is before my period dimension, that works when I do it manually. However I cannot work out how to do that in a macro

Need help with dynamic expressions in a chart

johnw — Fri, 07 Jan 2011 19:42:28 GMT

I agree that the APIguide is pretty confusing, and trust me, it's much better than it used to be. Ugh.

I can't remember how to move a dimension to the top in a pivot table, if that's what you're asking for. I seem to remember some issue with it, but I think it was just some formatting issue rather than any actual problem putting it on top. I seem to remember that it was a little inobvious, like instead of setting some "put it on top" parameter, you had to put the fields in a specific order, and then tell it the NUMBER of fields that should be either on the left or on top, or something like that. I'm sure it's been discussed on the forum before, so maybe searching will turn something up. Sorry I'm not more helpful.

Need help with dynamic expressions in a chart

diffeyj — Mon, 10 Jan 2011 00:03:48 GMT

thanx for all the help John. I have found some topics on the forum that have helped me out. It all comes down to knowing what to look for. Once I found the http://community.qlik.com/forums/t/24381.aspx post which told me how to use the number of left columns and stuff. It worked a treat.

cheers again