topic Re: List box selection to pick class() results in QlikView

List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 08:35:28 GMT

Hello All,

I have a list box that uses the class function in order to select a particular range in a bar chart, etc.. However, when I select the value the other options disappear rather than just greying out. I assume this is because it's an expression rather than a dimension and it'll only show the 'available' ones. Does anyone know how I can keep all the options being displayed?

My expression is this:

class(aggr(

(isnull(FP_tr_effdate)='-1',0,

(isnull(ISS_tr_effdate)='-1',num(today()) - num(FP_tr_effdate),

num

(ISS_tr_effdate)-num(FP_tr_effdate)))

ISS_tr_ref),$(vAge),'Age')

Many thanks for your help!

Kind Regards,

Dayna

Re: List box selection to pick class() results

qliksus — Tue, 12 Jun 2012 09:53:38 GMT

Remove the aggr function and try

class(

(isnull(FP_tr_effdate)='-1',0,

(isnull(ISS_tr_effdate)='-1',num(today()) - num(FP_tr_effdate),

num

(ISS_tr_effdate)-num(FP_tr_effdate)))

),$(vAge),'Age')

Re: List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 10:25:55 GMT

Hello Qliksus,

When I remove the aggr() from the expression my selections are incorrect – as it looks like it’s picking products in that range and it needs to select pallet references. Sorry!

Re: List box selection to pick class() results

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 10:39:07 GMT

Hello Dayna,

That happens because you are using the Aggr() function alone, so you actually aggregate values, and therefore the other values are not displayed. Check this thread and this other thread on how to work that around by using Match() to return a full list of values.

Hope that helps.

Miguel

Re: List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 12:21:51 GMT

Hello,

Jonathan, I don’t think I can – the values are in two different tables so I might be able to bodge it, but ideally I’d like the bucket size to be dynamic.

Miguel, many thanks for those links, they look very interesting! I’ve never used the match() function and the help guide wasn’t very useful but your link shows what it can achieve. I must admit, I’m slightly confused how to apply it to my scenario.. Would the class() function still work?

Something like:

=if(match(ISS_tr_ref, class(aggr(

if(isnull(FP_tr_effdate)='-1',0,

if(isnull(ISS_tr_effdate)='-1',num(today()) - num(FP_tr_effdate),

num(ISS_tr_effdate)-num(FP_tr_effdate)))

,ISS_tr_ref),$(vAge),'Age')) ?

Many thanks for your help!

Kind Regards,

Dayna

Re: List box selection to pick class() results

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 13:16:40 GMT

Hi Dayna,

That should work, although I'm not sure on the exact code to use. Could you update some dummy data?

Miguel

Re: List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 13:43:13 GMT

Hello Miguel,

Hopefully this attaches! I’ve added an application with sample data holding the list box and pivot table with the expression.

Thank you for your help!

Kind Regards,

Dayna

Re: List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 13:56:12 GMT

Hello Miguel,

Hopefully this attaches! I’ve added an application with sample data holding the list box and pivot table with the expression.

Thank you for your help!

Kind Regards,

Dayna

Re: List box selection to pick class() results

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 14:18:59 GMT

Hello Dayna,

I've used a different solution to Match() because the expression does not correspond to an actual dimension but a combination of dimensions. Using Only({1} ) seems to work. Check attached anyway.

Hope that helps.

Miguel

Re: List box selection to pick class() results

Dayna — Tue, 12 Jun 2012 14:28:03 GMT

Hello Miguel,

Many thanks! It looks good on the example but I’ll try it with my application.

What is the only() doing? It seems to still respond to the reference field which is perfect.

Kind Regards,

Dayna

Re: List box selection to pick class() results

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 14:33:19 GMT

Hi Dayna,

The Only() function is an aggregation function intended to return only one value according to its name. I'm using this not because the Only() (the Aggr() function works well with aggregation functions as Sum(), Count() and Only()) but spexially because the {1} that, as you probably knows, means "all values regardless the state of selections". That kind of "forces" Aggr() to do the aggregation (and therefore the chart changes) but also keep all values.

Hope it makes at least a little sense.

Miguel

Re: List box selection to pick class() results

Dayna — Wed, 13 Jun 2012 13:03:20 GMT

Hello Miguel,

It looks good but I need to do some more testing. There will be examples where we might have more than one identical pallet reference in the date range – I’ve noticed that the current expression doesn’t include it. I assume I need to alter my aggr() dimensions to incorporate this logic?

Many thanks for all your help!

Kind Regards,

Dayna

Re: List box selection to pick class() results

Miguel_Angel_Baeyens — Wed, 13 Jun 2012 13:35:46 GMT

Hello Dayna,

Not sure what you mean. Something like this?

Hope that helps.

Miguel

Re: List box selection to pick class() results

Dayna — Thu, 21 Jun 2012 13:44:48 GMT

Hello Miguel,

As it ends up, I shouldn't have more than one pallet reference so thank you very much for your help!

If you could assist with another issue of mine it would be appreciated (linked to this data) to only get the last record within the load script?

If you could help, I've raised a topic here:

http://community.qlik.com/thread/55876

Thank you again!

Kind Regards,

Dayna