Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have my below input table for monthly target
Year | Month | Country | Sales Target |
2019 | 1 | India | 122 |
2019 | 2 | India | 134 |
2019 | 3 | India | 149 |
2019 | 4 | India | 158 |
My requirement is for weekly target by weekending on Sunday.
If the weekending is on 1st or 2nd of a month then previous month target will be taken into account
If the weekending is on 3rd of a month onwards, then current month target will be taken in place.
My desired output is as below: (It's a bit complex, so kindly help me as I am stuck since last 2 days)
Year | Month | Country | WeekEnd | Sales Target |
2020 | 1 | India | 05.01.2020 | 122 |
2020 | 1 | India | 12.01.2020 | 122 |
2020 | 1 | India | 19.01,2020 | 122 |
2020 | 1 | India | 26.01.2020 | 122 |
2020 | 1 | India | 02.02.2020 | 122 |
2020 | 2 | India | 09.02.2020 | 134 |
2020 | 2 | India | 16.02.2020 | 134 |
2020 | 2 | India | 23.02.2020 | 134 |
2020 | 2 | India | 01.03.2020 | 134 |
2020 | 3 | India | 08.03.2020 | 149 |
2020 | 3 | India | 15.03.2020 | 149 |
2020 | 3 | India | 22.03.2020 | 149 |
2020 | 3 | India | 29.03.2020 | 149 |
2020 | 4 | India | 05.04.2020 | 158 |
The same has been achieved using a joining key and a date calendar having max date and min date. Then using while, pick up all dates for week end.
Then have a join and provide a condition over there to achieve this
You would need to find the month and year of each Friday in the desired period.
In the below expression I calculate the month of the Friday in the fifth week of 2019.
Month(MakeWeekDate(2019, 5 , 4) ) as Month,
Year(MakeWeekDate(2019, 5 , 4) ) as Year
Try to utilize these in order to get the right associations to your monthly week targets.
If I have to script the same for the small data set which I have provided how to implement the same?
I did not get your logic.
The same has been achieved using a joining key and a date calendar having max date and min date. Then using while, pick up all dates for week end.
Then have a join and provide a condition over there to achieve this