topic Re: User-Input Date for Reporting in Alteryx Designer Desktop Discussions

User-Input Date for Reporting

jeffjose33 — Thu, 13 Jun 2024 21:53:07 GMT

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.

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!

Re: User-Input Date for Reporting

Joe_Lipski — Wed, 20 Oct 2021 19:27:03 GMT

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.

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

Re: User-Input Date for Reporting

jeffjose33 — Wed, 20 Oct 2021 21:24:15 GMT

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

Re: User-Input Date for Reporting

jeffjose33 — Thu, 21 Oct 2021 16:19:55 GMT

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

Re: User-Input Date for Reporting

Joe_Lipski — Fri, 22 Oct 2021 10:57:24 GMT

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

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:

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

Please let me know if this helps?

Re: User-Input Date for Reporting

jeffjose33 — Fri, 22 Oct 2021 14:17:06 GMT

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.

Re: User-Input Date for Reporting

Joe_Lipski — Fri, 22 Oct 2021 15:53:16 GMT

Pleased that I can help!!