topic Re: Entering date as column headers in Alteryx Designer Desktop Discussions

Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 06:44:17 GMT

Hello Team,

I have listed all my requirements, input data as well as expected output in attached excel. Please help.

Re: Entering date as column headers

Jonathan-Sherman — Mon, 06 Jul 2020 07:15:48 GMT

This could be achieved by creating the dates, followed by cross-tabbing.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

Re: Entering date as column headers

DavidP — Mon, 06 Jul 2020 07:19:38 GMT

This should do it - you may have to tidy up the column names.

Re: Entering date as column headers

grazitti_sapna — Mon, 06 Jul 2020 07:41:15 GMT

Hi @BhatiaSakshi , here is my solution to your post. I hope this helps.

Thanks.

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 11:12:43 GMT

Thank you so much for this....just one concern I need the period starting from month end of running date of workflow...for example If I run this workflow period should start from 31-July-20.

so only future periods should be captured.

Also amounts should get reflected in the period which ever comes first for that quarter. For eg. If I run this workflow next month periods should start from 30-Aug-20. and amount for that quarter will populate in that month for that quarter. (so amounts would come in the first month for that quarter)

Hope I am clear!!

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 12:48:13 GMT

I am posting the query again to explain better. Sorry for the inconvenience.

Re: Entering date as column headers

DavidP — Mon, 06 Jul 2020 13:51:59 GMT

Please see updated version attached.

Re: Entering date as column headers

Jonathan-Sherman — Mon, 06 Jul 2020 13:52:40 GMT

Hi @BhatiaSakshi,

I've updated my workflow to solve the additions you had:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

Regards,

Jonathan

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 14:52:41 GMT

Hi David, I cant see the entry date adjustment for first quarter in attached workflow.

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 14:57:22 GMT

Hi Jonathan, I cant see entry date adjustment in your workflow. Please suggest. Attaching the query file again.

Re: Entering date as column headers

Jonathan-Sherman — Mon, 06 Jul 2020 15:09:06 GMT

Hi @BhatiaSakshi,

You said you wanted to only see months >= date you ran the workflow? i.e. running the workflow today you'd see 2020-07-31 onwards, nothing prior to July?

Regards,

Jonathan

Re: Entering date as column headers

DavidP — Mon, 06 Jul 2020 15:47:34 GMT

The tile tool and formula tool after it ensures that the amount is always included in the 1st month, regardless whether it’s julu August or September, for instance.

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 16:00:34 GMT

Hi Jonathan, Yes you got me right I only need future period. but going forward I would need this adjustment in workflow. I have amended the query data. Can you please help me how to put this adjustment.

Also in earlier workflow there were 2 months missing.

Thanks in advance!!

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 16:10:17 GMT

Right David but for first quarter, I need amount as per the month of entry date. If you see my query (attaching again) for first quarter you will see for first quarter numbers are split basis the entry month.

Re: Entering date as column headers

BhatiaSakshi — Mon, 06 Jul 2020 18:38:20 GMT

Please look into my query.

Re: Entering date as column headers

DavidP — Mon, 06 Jul 2020 22:25:42 GMT

Hi @BhatiaSakshi,

I think I'm very close to understanding the logic, but I have a couple of questions. From your data:

1. The last 2 columns of the Input data are both Balance Q3 2021 - is this a typo? Should the last column be Q4 2021?

2. My understanding is that the 1st quarter is the quarter that the workflow is run in, so currently it will be Q3 2020, as shown below.

3. For all other quarters I should ignore the entry date and just put the full amount in the 1st month of the quarter.

4. If the entry date falls within the first quarter, the full amount should be in the month of the entry date.

Your example Output shows that if the entry date is after the first quarter, the full amount should be in the last month of the first quarter - is this correct?

The example workflow attached has the logic implemented as described above, to match your example.

Re: Entering date as column headers

BhatiaSakshi — Tue, 07 Jul 2020 13:13:38 GMT

Thank You so much David!! It worked

Great help

Re: Entering date as column headers

BhatiaSakshi — Thu, 09 Jul 2020 11:34:20 GMT

Hi David, Can I ask you a quick thing in this workflow.

If not only in first quarter but for all quarters I need to consider the entry date. For Eg. If entry date is "04-28-20" amount will go in 04-30-20" and all remaining numbers in last month of quarter.

What additional step you will add?

Re: Entering date as column headers

DavidP — Fri, 10 Jul 2020 06:25:22 GMT

Hi @BhatiaSakshi,

In the attached version, the rules are:

for all months prior to entry date, the value is 0

where month = month of entry date, show full value

if entry date < quarter, show full value in 1st month of quarter

Is this what you're looking for?

Re: Entering date as column headers

BhatiaSakshi — Mon, 13 Jul 2020 15:51:05 GMT

Hi David, Sorry for the late response!!

I am attaching the expected output now. In nutshell I am looking for all the numbers should fall in month of entry date and remaining ones should fall in last month of the quarter.