Button Dropdown - writing variables in set value

Hi All,

I am creating a button dropdown in my analysis and I am trying to enter a formula into the set value option, so that when the user chooses on a specific filter down option, the analysis will update with the correct information.

I am having trouble getting this to work. I have tried using the below codes:
FILTER “Cases”.“PaymentTerms” in (‘9302’,‘9307’,0329’)
or
CASE WHEN “Cases”.“PaymentTerms” in (‘9302’,‘9307’,0329’) THEN “Cases”.“PaymentTerms” ELSE NULL END
however, neither update the data when selected.

Please help!

Also, I tried building out the logic in a simple drop down component:

CASE WHEN
“Cases”.“PaymentTerms” in (‘9302’,‘9307’,0329’)
THEN ‘Payment Term A’
WHEN "Cases”.“PaymentTerms” in (‘9302’,‘9307’, ‘9157’,‘9300’)
THEN ‘Payment Term B’
ELSE ‘Other’
END

However, occurrences where payment terms can be in both bucket A & B, only bucket A contains the correct selection.

Thanks,
Anna

Hi Anna,

The applied formula for you button dropdown FILTER “Cases”.“PaymentTerms” IN (‘9302’,‘9307’,'0329’) seems correct the way you put it.

What you now need to do in order for your data to change is to modify the sheet filter/component filter for your entire analysis sheet/components to show the filtered data. You would then simply create a sheet filter/component filter containing the created variable: <%=DRILLDOWN%> .

If you now select a particular payment term from you button dropdown, the entire sheet/component should also change.

Best regards,
Viana

Thanks Viana,

Is there a way to apply this to the entire analysis and not just the sheet/component?

Cheers,
Anna

Hi Anna,

unfortunately, it is currently not possible to set a global filter.

Best regards,
Viana

Hi Viana,

Would it be possible using the drop down filter?

For example, using this statement:

CASE WHEN
“Cases”.“PaymentTerms” in (‘9302’,‘9307’,0329’)
THEN ‘Payment Term A’
WHEN "Cases”.“PaymentTerms” in (‘9302’,‘9307’, ‘9157’,‘9300’)
THEN ‘Payment Term B’
ELSE ‘Other’
END

I know the code works when i want to segregate completely distinct groups, but fails if there are certain payment terms in more then one bucket. Is there a different piece of code that i can try that would solve for this?

Hi Viana,

Have you had a chance to investigate this question?

Cheers,
Anna

Hi Anna,

if I understand you correctly you want the viewers to be able to select ‘payment term A’ from a button drop down list and then to have the analysis filtered to only include cases with Payment terms in (‘9302’,‘9307’,0329’). If they instead choose ‘payment term B’ the filter should only allow Payment terms in (‘9302’,‘9307’, ‘9157’,‘9300’)?

I assume you want to the viewer to be able to see if they currently have Payment Term A or B selected so I have included this in the solution but it’s not really necessary.

This is how to set up the button and the filter.

  1. Create two variables called “PaymentTerms_list” and “PaymentTerms_name”.

  2. Make a ‘Button dropdown’ Component. (‘Manual Input’ should be set as the default, if not choose it from the dropdown). Next to “title” write something like “Payment term currently selected: <%= PaymentTerms_name %>”.

  3. Click ‘Add dropdown entry’. Next to “Button Title” then write ‘Payment Term A’. Set “Button Action” to “Set Variable”. Select the variable to “PaymentTerms_name” and write ‘A’ in the box.

  4. Then click on “Add another variable”. Then in the new box that appears set the variable to “PaymentTerms_list” and in the box write the payment terms for category A (i.e. '9302’,‘9307’,0329’).

  5. Repeat 3 and 4 for Payment Term B. (and any other payment terms you want to add at any point)

  6. Apply this filter where desired:

FILTER “Cases”.“PaymentTerms” IN (<%= PaymentTerms_list %>);

Does this solve your problem?

Best wishes,

Calandra

Hi Calandra,

Thank you for your response. This works if I would like to apply the filters to a couple of sections in the analysis, however, it feels a bit tedious, if I would need to do this for all the sheets in each of the components in the analysis.

Would it be ok if you gave me a call please? Perhaps it would be easier to explain what I am trying to achieve :slight_smile:
+44 (0) 7825 756905

Thanks,
Anna

Hi Anna,

hang on, I’ll send you a Zoom invite.

Best wishes,
Calandra

Hi anyone reading this,

Anna wanted a solution where it was possible to switch off filtering by payment terms.

What ended up working was to use the variable set by the dropdown to change the filter statement itself not the value in the filter statement.

So the actual filter was changed to:

<%=PaymentTerms_list%>

And the value in the dropdown list now contains the filter statement itself.

So in Payment Terms A, the value was changed to

FILTER “Cases”.“PaymentTerms” IN (‘9302’,‘9307’,0329’);

In Payment Terms B the value became

FILTER “Cases”.“PaymentTerms” IN (‘9302’,‘9307’, ‘9157’,‘9300’);

‘Add dropdown entry’ was used to create a new category: “All payment terms”.

The value that it write the to the variable “PaymentTerms_list” is:

FILTER “Cases”.“PaymentTerms” NOT IN (‘ ');

As there are no payment terms called ‘ ', cases don’t have that payment term so the filter lets through absolutely all cases.

I hope this helps anyone is a similar situation.

Best Wishes,

Calandra