How to add a conditional filter to a component filter (load script)?

Question

Hello everyone,

I am writing here because I couldn´t solve my issue with other questions asked.

My Situation:

I have an OLAP-Table with a lot of Data and dates regarding finances.

Now I want to add an Input Variable where User can input a single date.

Depending on that input, the OLAP-Table should change (2 columns)

The field is OPTIONAL and I am struggling with handling the case of "null" as input.

My current idea:

FILTER "Table"."SomeDate" >=

CASE WHEN <%=DATE_OPEN_ITEMS%> is not NULL

THEN TO_DATE( <%=DATE_OPEN_ITEMS%>, FORMAT ( '%d-%m-%Y' ) )

ELSE ignore the filter all together

END

;

Now if I type in '31-12-2022' it works great!

But 1. If the input field is empty there is an error

and 2. the User has to use ' ' while typing it in.

Any ideas?

Thanks!

janpeter.van.d · Accepted Answer

Hi Milan,The simplest solution that might work would be to create a Saved Formula (in Analysis)/ KPI (in Knowledge Model) that wraps your variable in between brackets. Kind regards,Jan-peter

milan.eric · Answer

I managed to do it like this:FILTER ((ISNULL(<%=DATE_OPEN_ITEMS%>)=0) AND "Table"."SomeDate" >= TO_DATE( <%=DATE_OPEN_ITEMS%>, FORMAT ( '%Y-%m-%d' ) ) AND "Table"."SomeDate" <= TO_DATE ( <%=DATE_OPEN_ITEMS%> , FORMAT ( '%Y-%m-%d' ) ))OR (<%=DATE_OPEN_ITEMS%> = ' ') AND "Table"."SomeDate" >= TO_DATE( '1900-01-01', FORMAT ( '%Y-%m-%d' ) ) AND "Table"."SomeDate" <= TO_DATE( '2999-12-31', FORMAT ( '%Y-%m-%d' ) );Problem 2) persists. The User has to type in ' ' for it to be "Null" and every date has to be in apostrophes. Not sure how to fix that.Any help?

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN