Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Ron1
Partner - Creator
Partner - Creator

Conditional Month into Weekly Targets

I have my below input table for monthly target

YearMonthCountrySales Target
20191India122
20192India134
20193India149
20194India158

 

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)

YearMonthCountryWeekEndSales Target
20201India05.01.2020122
20201India12.01.2020122
20201India19.01,2020122
20201India26.01.2020122
20201India02.02.2020122
20202India09.02.2020134
20202India16.02.2020134
20202India23.02.2020134
20202India01.03.2020134
20203India08.03.2020149
20203India15.03.2020149
20203India22.03.2020149
20203India29.03.2020149
20204India05.04.2020158
1 Solution

Accepted Solutions
Ron1
Partner - Creator
Partner - Creator
Author

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

View solution in original post

3 Replies
Vegar
MVP
MVP

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.

Ron1
Partner - Creator
Partner - Creator
Author

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.

Ron1
Partner - Creator
Partner - Creator
Author

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