Filter with conditional statement

[Robertinho2077]

Hello Guys,

I am trying to create a filter with a condition.

I have a table like below:

Columns: ID, Store, InativeAt, TotalSales

and I have a list where I can choose the years. So I'm trying to apply the following filter:
If InativeAt IS NOT NULL AND YEAR of InativeAt  is lesser than SELECTED YEAR Set TotalSales as 0 

This must not show the real value of TotalSales when the year selected is greater than the year of inativation of the store.

I try to apply the statement on property of the table later column but I haven't sucessfully result, someone could help me with this?

[ignacioroman]

I guess in your table you have a date variable, lets assume is "year_total_sales"

You have to upload an auxiliary date table. If you are working just with year, you can upload it from Excel or inline

Do it like this

Aux_date:

Load 

year as [Aux year]

from "Excel"

in your expression, you must put this

sum(if(year_total_sales<[aux year] or InativeAt='',0,TotalSales)) 

And in the filter, put the variable [Aux year] instead year_total_sales.