Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Robertinho2077
Contributor
Contributor

Filter with conditional statement

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?

 

Labels (2)
1 Reply
ignacioroman
Contributor II
Contributor II

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.