Create New columns & respective formulas based on Input

[ab1275]

Need Help!

I have to create a report that will look at period based on the period input specified. The easiest way that I can think of is a period input file with a start date and end date. Suggestions welcome on any other method. (example a pop up where user specifies start/end date etc)

The main file has many columns but the required fields in the main file are:

Group #         Valid From Date     Valid To Date          Locked?

12344                7-1-2014                6-30-2016                No

12344                7-1-2016                12-31-9999              Yes

56785                1-1-2020                7-31-2020                No

56785                8-1-2020                12-31-9999              Yes

85297                9-1-2020                12-31-9999              No

Valid From will always be the first day of the month and valid To will be the last day of the month.

Locked indicates if the Group # is locked for new customers or not in the period between Valid From - Valid To.

This report will specify how many groups were open for new customers in the month specified.

I would like to create new columns for the months specified in the period input file. Example if period input file said start is July 2020 and end is Sept 2020, new columns would be July 2020, Aug 2020, Sept 2020. 

For each column the count should be if the group # is open for new customers in the month. In the above example

July 2020 would show 1 (for 56785)

Aug 2020 would show 0 

Sept 2020 would show 1 (for 85297)

How can I accomplish this? 

[Qiu · Accepted solution]

@ab1275 
Happy New Year.

First reply for #2021.

[ab1275]

This helps a ton! Thank you. Is it possible to have the output in the below format?

                        July2020   Aug2020   Sept2020

open groups         1                 0                1

[phoebe_kelley · Accepted solution]

I think I got it. Thanks @Qiu for the workflow, just added onto that 🙂 Just a heads up that the column names will be in alphabetical order rather than date order. You can add a select after to rearrange them, or add a prefix before the crosstab to get them to show up in the order you want.

[ab1275]

Thank you Qiu & Phoebe! I would have never been able to do it on my own! Truly appreciate all your help!

[Tyro_abc]

Hi @ab1275 

Here is what I am thinking, because your validity range starts from first of month and runs through end of any month, it would be enough to compare at month level, i.e. we do not need to go down to individual date level.  Please have a look at attached workflow. Slight trick we need to apply while generating month ranges with Generate Rows tool, let me know if you have any question.

Happy New Year!

This way, we would not have any issue with Column Ordering.

Best Regards

Arundhuti