Filtering tables based on the dates

[su_pyae]

Hi, 

I am using variable input to get the user-defined date. Based on that date, I want to see which orders are delivered or not and calculating the amount if the orders are still open. 

So, my calculation is like this: If the date filter is equal or larger than the order date AND the date filter is smaller than the delivery date, calculate the amount. So, my table looks like this right now. I have other dimensions in the table as well. 

Date Filter = 04/04/2020

User ID	Delivery Date	Order Date	Amount	Explanations
Total			$150.00
1	~	04/04/2021	$0.00	This shouldn't show up since this order hasn't been created on 04/04/2020
2	02/10/2020	02/04/2020	$0.00	This order has been fulfilled so this row shouldn't show up either.
3	~	04/04/2018	$50.00	I need these rows since the order hasn't been fulfilled.
4	~	08/04/2019	$10.00	I need these rows since the order hasn't been fulfilled.

The calculation seems to be working well. The only problem is that I need to get rid of those irrelevant rows. 

I try to filter out for the dimensions using the if statements and not including null values. And they don't work. 

I think about filtering the rows if the amount is 0 but I don't know how to do it properly. 

[anthonyj]

Hi,

I created a variable (vDate) which can be altered with the variable input object and added "Amount" with a set analysis.

Sum({$<[Order Date]={"<$(=date(vDate))"}, [Delivery Date]={"=len([Delivery Date])=0"} >}Amount)

These are the results from testing.

Regards

Anthony

[su_pyae]

Hi, Anthony!

Thank you for the reply. Actually, the delivery date is not blank. It has an  impossible date like (12/31/9999). 

The amount is using a formula like Sum(Another_Column) to get the amount. 

I am still not familiar with the set analysis and I am not sure where I can put the set analysis formula you have mentioned above. 

Thank you in advance. 

[anthonyj]

Hi,

To take into account the impossible date we just need to change the second part of the set analysis.

Sum({$<[Order Date]={"=<=$(=date(vDate))"}, [Delivery Date]={">$(=date(vDate))"} >}Another_Column)

This would take into your account your description on how it should work however there is a possibility that date could be entered that is between an Order Date and legitimate Delivery Date. 

Eg Ordered on 01/08/2021 Delivered 20/08/2021 and you enter a date that is 10/08/2021.

This would make that record show up however it sounds like your requirement for only displaying Open records as marked by future high dated deliveries may be that the Date filter needs only to be equal to or larger than the Order Date and that the Delivery date is greater than today's date.

That would look like this:

Sum({$<[Order Date]={"=<=$(=date(vDate))"}, [Delivery Date]={">$(=today(0)"} >}Another_Column)

When creating your table object if you add this in as a Measure along with the other dimensions you added then native Qlik functionality will hide any of the User ID's that don't meet this condition.

Let me know if you need any further clarification.

Regards

Anthony

[su_pyae]

Hi Anthony, 

thank you for the clarification. I used the first set expression you provided because what I need is the records if their order date <= vDate and delivery date > vDate. 

After using that expression, my table looks like this now. 

User ID	Delivery Date	Order Date	Amount
-	-	-	$0.00

This is how my I set up the date filter for my input box:

Date(Date#('$(vDate)', 'MM/DD/YYYY'), 'MM/DD/YYYY')

Regardless of whether I used date(vDate) or just vDate in the expression, the table still looks the same. 

Thank you for your help!

[su_pyae · Accepted solution]

Hello Anthony, 

I was able to solve the issues. I used the same formula I have before (checking the conditions with "if"). Then, I unchecked "include zero values" from Add-ons tab of the table. 

Thank  you so much for brainstorming with me. 

Sincerely, 

Su