Divide the values on weekly basis which are shown monthly.

[stj1120]

Hi all,

I have a materials projection dataset. In this dataset each material is displayed with stock from Jan 2021 until Dec 2022. We can notice the month wise records through Fiscal year/period column. 

I'd now need the values of U1 and U3 for each material should be divided weekly. Currently they are shown monthly. The number of weeks for each month (Fiscal year/period) should be detected dynamically.

I tried almost but, when I created the dates using the Generate Rows tool, the number of records I obtained seemed to be incorrect and also I think the data pattern was also incorrect.

Could you please check and help to correct the workflow ? 

Thank you.

Best Regards,

Teja.

[stj1120]

@atcodedog05, @afv2688 , @danilang , @mceleavey  could you please help me with the  solution to the above query?

[Christina_H]

@stj1120 What dates are you trying to generate?  Your workflow calculates start date as the first day of the year based on the fiscal year, and the end date as the last day of the current month (i.e. last day of November 2021 for every row) and then tries to generate rows for all dates between start and end.  Where the fiscal year is 2022 it generates no rows (start date > end date), for 2021 it generates 334 rows (total days in 2021 up to the end of November).

[stj1120]

Hi @Christina_H 

Thank you for your response.

I'm now trying to generate the dates for the year 2021 to  get the weeks. After that, I need to divide U1, U3 values based on the number of weeks present in a month. 

Could you check and help in this regard?

Thank you.

Best Regards,

Teja.

[Christina_H · Accepted solution]

If I understand correctly, you don't need to generate every day of the year, just every week.  Try this, I generate dates for every Monday from the start to the end of your data, count the Mondays per month, then join everything together and divide each months figures by the number of Mondays.

[stj1120]

Hi @Christina_H 

After generating the dates for every Monday then from start to end of the data could you also help me to divide the monthly QU1 and QU3 value from Second Monday of the current month until when the First Monday week of the next month which we will treat it as one complete month.

I mean we need to divide them based on the number of weeks present between Second Monday of the current month until when the First Monday week of the next month.

The reason is that QU1 and QU3 values for every month would be released on every second monday of the month and hence, to divide these QU1 and QU3 values we should consider from second monday week of the current month until the first monday week of the next month. 

Hope you understand! Could you kindly help me how do we write the logic to calculate this in Alteryx?

Thank you.

Best Regards,

Teja.

[Christina_H · Accepted solution]

Hi @stj1120 

I've made changes as follows:

1. Changed start date calculation to accurately start on Mondays - I initially entered a fixed date for the first Monday in 2021, then made a last minute change to calculate the start date from the input data but forgot to update my explanation!

2. Changed the month and year calculations on the generated dates to offset them by a week, so the Mondays that are counted in e.g. April 2021 go from April 12 to May 3.

Christina

[stj1120]

HI @Christina_H 

Thanks a lot. It worked like a charm.. 😀