Variable to filter range

[Former Member]

Hey everybody,

I want to create a variable "VAR_DATE" to filter a range of my dataset, based on the input of the user.

In my example I have a table with three columns:

"date"     "date-36 month"     "comment"

the variable is filtering the column "date". as a result column date should be filtered from the value "date-36 month" like a min to "date" like a max.

I tried to use an If statement within the expression but it didnt work.

I hope somebody can help 🙂

Regards

[lucas_oliveira]

Hi Christoph,

Can you elaborate your request a little bit more?  Please provide more details (your code, samples, examples, etc) so we can try to help further.

I'm assuming you have an information model (analytic/calc view) with 3 output columns and the variable will be used to filter 2 of the output columns. Did I get this right?

What exaclty are you looking for in terms of filtering?

Regards,

Lucas de Oliveira

[Former Member]

Hi Lucas,

thats right! I´m using a calculation view, where I want to set a filter on a column "YYYYMM". This is actually a string. My goal is to set a parameter, for example '201010' and based on this filter a range of the last 36 month. Due this is not possible with a string, I converted the "YYYYMM" in to a actual date filed and added a corresponding date field, which shows the date 36 ago "YYYYMM_36".

So I want now set a range based on this two values. Show all values from
"YYYYMM" (date) to "YYYYMM_36" (date) based on the input of the string "YYYYMM" (varchar)

But I´m not sure how to set the expression for the variable.

Best regards, Christoph

[former_member182114]

Hi Christoph,

Still confusing for me, sorry.

You have on source a column in format YYYYMM and a calculated column YYYYMM_36 that was based on YYYYMM converted to date back to 36 months ago and converted back to string. Right?

Now if want filter what is parameter >= YYYYMM_36 and <= YYYYMM. Is what you want?

What is the doubt? How to use parameters?

How are you you calling this view? You put the filter on WHERE clause or are you passing it by placeholder?

Regards, Fernando Da Rós

[Former Member]

Hi Fernando,

exactly. Base column is YYYYMM (varchar) --> format was requested by enduser. Enduser wants to filter the last 36 month based on this field. So I created two date fields (date1 and date1 - 36months). Then I created the variable on this field, to set a filter. I tried to use an expression, but it didnt work. So I wonder if thats the right way to filter between input date and the last 36 month.

Regards, Christoph.

[former_member182114]

Hi Christoph,

Variable work only "outside" the view so it exist on where clause and so you can't achieve what you want passing only one information. What you can do is pass a range but in this case user that will calculate.

You can use input parameter which work "inside" the view and in this case you can do calculations and filter as earlier as possible producing the desired effect.

Example:

- define one input parameter P_YYYYMM NCHARVAR 6

- inside on your calculation you need put on expression a filter like

"YYYYMM" <= '$$P_YYYYMM$$' and

"YYYYMM_36" >=leftStr(replace(string(adddays(leftStr('$$P_YYYYMM$$',4) + '-' + rightStr('$$P_YYYYMM$$',2) + '-01',-1080)),'-',''),6)

where:

YYYYMM is the original column on format 201412

YYYYMM_36 is the calculated column you mentioned that alread exist

I didn't tested but it's a suggestion to you move forward. Check and adjust what is needed.

Regards, Fernando Da Rós

[Former Member]

Hi Fernando! Thank you. This helped a lot! Best regards, Christoph.