Need help with dynamic expressions in a chart - Qlik Community

diffeyj · ‎2011-01-05

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

matt_crowther · ‎2011-01-05

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

diffeyj · ‎2011-01-05

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

johnw · ‎2011-01-05

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".

diffeyj · ‎2011-01-05

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

johnw · ‎2011-01-05

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.

diffeyj · ‎2011-01-05

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

johnw · ‎2011-01-05

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.

diffeyj · ‎2011-01-05

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

diffeyj · ‎2011-01-05

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