Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

User-Input Date for Reporting

jeffjose33
7 - Meteor

I'm trying to create an analytic app where the user is able to select a date and then the data pulled from the database is "as of" that date. For example, if I go into the current JDE database we have, I can set the effective date as 12/31/2020 and then view the data as of that date. 

 

A little background for the process, I am trying to generate an exception report for a specific indicator we have in a JDE datatable. We want to be able to run the workflow monthly in order to see if any of the exceptions have occurred, but we also want to be able to go to past months and see what the data looked like then, to ensure that any changes actually made sense. 

 

jeffjose33_0-1634754679751.png

In my workflow, I'm pulling from a database and then selecting only the most recent entry for a specific entity by using descending date + unique tool. I'm having issues figuring out how to let the user enter the date (12/31/2020 for example) and then telling the workflow to only incorporate data for 12/31/2020 or prior. From there I believe I can simply order all dates and then select unique values, but I'm stuck on updating an end date filter based on what the user selects.

 

Thanks in advance!

6 REPLIES 6
Joe_Lipski
13 - Pulsar
13 - Pulsar

Hi @jeffjose33In your screenshot above you will either need to add a filter tool or an in database filter tool.

 

Your action tool after the date interface tool will then connect into the lightning bolt anchor of the filter/in-db filter tool and this should be pointing to a field which is 'end date'

 

I've attached an example. Make sure you check out the configuration of the action tool.

 

Joe_Lipski_0-1634758004951.png

 

Please let me know if this helps, or if I've misinterpreted!

 

Joe Lipski
jeffjose33
7 - Meteor

Thanks @joe_lipski, I must've accidentally removed the filter tool before my screenshot.

 

My question is really revolving around what expression I put in the filter. So assuming I have a field called End_Date that I want to manipulate, and I set up the same interface tools you've shown to have the user select a date, what kind of expression would I use to say "Filter any end date value that is prior to 12/31/20 as TRUE and the remaining FALSE". So that way the records I have will only be related to those prior to that date. Thanks

jeffjose33
7 - Meteor

Thanks @joe_lipski, I must've accidentally removed the filter tool before my screenshot.

 

My question is really revolving around what expression I put in the filter. So assuming I have a field called End_Date that I want to manipulate, and I set up the same interface tools you've shown to have the user select a date, what kind of expression would I use to say "Filter any end date value that is prior to 12/31/20 as TRUE and the remaining FALSE". So that way the records I have will only be related to those prior to that date. Thanks

Joe_Lipski
13 - Pulsar
13 - Pulsar

Sorry for the slow response @jeffjose33 

 

I've updated the workflow here and included screenshots of each part.

 

Filter: The date is a PLACEHOLDER and will be replaced by the action tool

 

Joe_Lipski_0-1634900086099.png

 

Action Tool:

 

The date in the 'replace a specific string' box needs to match the date in the step above as this placeholder is what is being removed:

 

Joe_Lipski_1-1634900149975.png

 

Output from Filter: You can see below the data is only showing me data after the 1st of November!

 

You could make it >= in the filter if you want

 

Joe_Lipski_2-1634900181317.png

 

Please let me know if this helps?

 

 

Joe Lipski
jeffjose33
7 - Meteor

Joe, thank you so much!! I wasn't understanding that the filter was acting like a placeholder and so I was trying to somehow reference the user input without understanding it was being replaced by the Action tool.

Joe_Lipski
13 - Pulsar
13 - Pulsar

Pleased that I can help!!

Joe Lipski
Labels