Analytic App Questions

Question

I have two questions on the attached example app-

1- I have an analytic app that asks the user select the last twelve months from an upstream set of data. I want to use that same selection in a formula tool at the end of the workflow to perform an average calculation over the last 12 months as shown in the attached workflow. The issue I am having is that in the second analytic section where it feeds into the formula tool I need to have brackets around each field for it to work properly. I currently have it constructed so the user has to select the last 12 months twice when running the workflow as an analytic, which isn't ideal. Is there a better way so I can manipulate the results from the first app selection so it works in the formula downstream and the user only has to select the last 12 months once when the app is run as an analytic?

2- The average formula tool at the end of the workflow is treating null values as 0 and not calculating the average correctly in those cases. How can I fix this using the formula tool?

Query Output.xlsx

Example TB Receive Date Query Analytic App.yxwz

AngelosPachis · Answer

Hi @AlexCron ,

So I have rearranged things a bit and made some changes to the workflow.

First of all, I used a single interface tool in the app, which is fed by all the different values you have in your input

Then, in the list box as you have done, I have selected to generate a custom list from the selected values, but I have added some quotation marks at the start/end of text and in between each selected value. Now if you select the values 202010 and 202011 and 202012, the string that will be formed by the interface tool will be

"202010","202011","202012"

This string is now set to update the expression "202011","202012" which is used in the filter tool shown below

to filter your records for the selected periods. Currently, the filter tool keeps records only for periods "202011","202012", but once the app runs that value will get updated to "202010","202011","202012" (if we follow the example given above)   and you will keep values for 3 periods now.

Final step is to use a summarize tool to average the different TB Days values. The values shown below are the averages if you select only two periods ("202011","202012") but you can run the app for different values to see if the results match.

Please let me know if the results shown above are those you are expecting and the summarize tool treats non-existing values as you expect it to.

Finally, at the end of the workflow, I have added a tool to test if the values given by the user are consecutive. So if the user selects "202009","202011","202012" instead, they will receive an error prompting them to select 12 consecutive months.

I would suggest adding another condition to that test tool, testing if the user has selected 12 periods in total.

Hope that helps a bit, let me know if that works for you.

Regards,

Angelos

PS The final pitfall that you should avoid is one regarding your input. You will notice that if you run the app once, you will see X number of available periods. Let's assume that you get the results you are looking for. Now the input gets updated and you have X+1 distinct periods in your dataset. This is where you have to be cautious. If you re-run the workflow, you wont be able to see that extra period that has just been added. The reason for that is that interface tools "occur" before the workflow runs. That means that the periods appearing in your interface tools are those from when the workflow run successfully for the last time, so the extra periods won't be included.

Your workflow would have to run once, for the app to pick up the new input, and the second time you run the app, the periods in the interface tool will be the right ones. The solution to that is a chained app I believe, but let's take it one step at a time.

Example TB Receive Date Query Analytic App_V2.yxwz

AngelosPachis · Answer

Hi Alex,

There are quite a few points to mention and a couple of pitfalls that your app may not work. But first thing first, the ultimate goal of the app is to allow the user to select certain months from the input and for the selected months, you want to estimate the average?

There is a part of the workflow that to me seems unecessary and can be deleted because it does not serve a purpose, or at least it's not clear to me yet; either way, out of the J output of your Join tool you get exactly the same values as out of the filter tool.

For the average calculation, have you though of using a summarize tool instead? That will eliminate the need for the brackets, the second interface tool and the formula tool.

Maskell_Rascal · Answer

Hi @AlexCron

You can connect your List Box to two different Action Tools. This will eliminate the need to have the user select the same values twice.

Regarding your second problem with the averages, you could put an IF statement in the formula to have it give a different calculation when one of the two values is a Null. Not sure what your expected output would be, so don't have one written that I can provide.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil