topic Variable field calculation in chart expression in QlikView

Variable field calculation in chart expression

Wed, 02 Dec 2015 14:12:20 GMT

Hi there!

Forgive my potential ignorance as I found similar posts but still have been unable to resolve.

I have a number of charts that give certain stats around service desk calls - for example, number calls per year/team/department etc (done via count of RequestIDs). But there are also charts for the time taken for these calls - for example total time spent per year/team/department (calculated via sum of WorkLogs, a request may have multiple WorkLogs).

Rather than repeat the same charts with either calculation I would like a variable and associated button(s) that gives the option for either calculation - for example click 'Time Spent' and see calcs for total time spent per team, or click 'Volume' for number number of calls per team.

If anyone has an idea then please share - would REALLY appreciate it

Re: Variable field calculation in chart expression

swuehl — Wed, 02 Dec 2015 20:06:48 GMT

You can maybe use conditional expressions to show the one aggregate or the other.

Have a look at the demo in the 'What's new in QV11.qvw' or search the forum for conditional expressions on how to setup this kind of charts.

Re: Variable field calculation in chart expression

thomaslg_wq — Wed, 02 Dec 2015 22:49:29 GMT

2 ways:

- Use expressions groups:

------ you need to have the two expressions in one chart, and then in expression tab, select the second expression and then select "group", apply and a button appears to change/select the expression you want to see

- as you suggest, create variable and buttons:

----- 1: you create a variable : settings-> variable overview -> create (name as example vViewExp1) / no value

------ 2 : you create 2 text objects with in "action" tab:

add->external->set variable-> variable: vViewExp1 and value : 0 for the first object and 1 for the second

------ 3: in your chart which has the two expressions, in expression tab, you see a condition input (conditional checkbox):

for the first expression write : vViewExp1=0 and the second vViewExp1 =1

Re: Variable field calculation in chart expression

Siva_Sankar — Thu, 03 Dec 2015 02:39:58 GMT

Dan,

Check here Dynamically Change Expressions and find the attached example.

Regards,

Siva

Re: Variable field calculation in chart expression

Thu, 03 Dec 2015 12:13:47 GMT

Thanks - had looked into before posting but couldn't find a matching solution. The search goes on

Re: Variable field calculation in chart expression

Thu, 03 Dec 2015 13:01:15 GMT

Hi Siva,

Thanks very much for passing on. Have now tried the metrics/pickmetrics method but having issues...

When I use the % prefix the island table loads and is visible using Ctrl-T and I can see it clearly with preview data. However, when I try and select it as a field in a list box, it does not appear as an option, like it doesn't exist - neither %metrics %pickmetrics are visible, despite not even getting to the hide % stage. So I also didn't add the expression to the chart because there is no point if I can't select it. Very strange.

When I don't use the %prefix the island table loads, is visible using Ctrl-T and can be selected as in a filter list. I use the expression which then allows me to use the filter to change the chart expression as expected but then the selection shows in Current Selections which will confuse users.

Unfortunately this method can't be used because it still only caters for an expression that varies only by field to be calculated and doesn't manipulate the calculation method - what I need is the flip between SUM(x) and COUNT(y)

Re: Variable field calculation in chart expression

Thu, 03 Dec 2015 16:36:23 GMT

I was able to get around the % issue, must have been my syntax or something.

Also was able to workaround the SUM vs COUNT in a very simple way. I added a "1 AS RequestCount" on the load so that I could sum the 1's to get the count - works like a charm!

Re: Variable field calculation in chart expression

Thu, 03 Dec 2015 16:44:03 GMT

Wow - that's such a simple way of doing things and works a treat! Thanks for passing on.

I couldn't use the first option because I needed to apply to multiple charts, however the second option works perfectly as it allows for using button control for multiple charts.

As both options work (conditional expression in chart and metrics/pickmetrics method suggested by @shankarace), I will need to evaluate which works best with other scenarios

Re: Variable field calculation in chart expression

thomaslg_wq — Thu, 03 Dec 2015 17:41:26 GMT

Shankarece is the best option for big apps and maintenance

Re: Variable field calculation in chart expression

Fri, 04 Dec 2015 10:02:50 GMT

I'm actually going to use a combination of both!

So the metrics/pickmetrics method will be used to enable a toggle between single expression variants - e.g. SUM(WorkLogHours)/SUM(RequestID).

Then I would also like to give the option make them relative e.g. as a percentage. So I will have a second expression with the same formula but expressed as %, and using the conditional expression in chart method.

This provides four buttons - Total Worklog Hours, Total Worklog Hours as %, Total Requests, Total Requests as %