List box selection to pick class() results

[Dayna]

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(

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

[qliksus]

Remove the aggr function and try

class(

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)))

),$(vAge),'Age')

[Dayna]

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!

[Miguel_Angel_Baeyens]

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

[Dayna]

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

[Miguel_Angel_Baeyens]

Hi Dayna,

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

Miguel

[Dayna]

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

[Dayna]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[Dayna]

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