I am aggregating the date for week level and it does not give me the value I want.
I have a week starting on sunday, 17 july, up to saturday, 23 july. if I insert the days in excel, with formula =isoweeknum(x), then I get that week 29 starts in 18. July and ends in 24. July.
in QS, if I get all days of this week and aggregate to week it shows me sunday, 17, which is week 28.
then I change the format to “W” and I get week 28. But here it is not considering all other days (mon-Sat), which is week 29, and therefore not the weeknum I want to see.
If I change the format to “w” it will give me week30, which means I have either wk28 or wk30. How do I get this period to show me week 29?
@Alexandre_Kniepert I agree, the formatting options are giving some strange results for Week. Honestly I wasnt even aware of ‘W’ format, I only knew ‘w’.
This is a workaround but you can write a calculated field that computes the number of weeks between the beginning of the first week of the year and the row value:
dateDiff(truncDate(‘WK’,truncDate(‘YYYY’, {order date})), {order date}, ‘WK’)
That seems to give me 29 for the week of July 17 2022.
the calculated field works all right, however my issue now is different. I am using another calculated field, WoW, which is: periodOverPeriodPercentDifference({DA sick (SumOver ds)},date,WEEK,1)
now, this function works only with a date field, and no calculated field, which means I cannot use the calculated field you described above, also because the WoW will not appear in the table when I remove the date field since they are linked with each other.
are there any other ways to calculate WoW without the periodoverperiod function? I would be able to do it in Power Query, but in QS I still havent found a way to use periods in the same way I would normally do (without pre-defined functions).
Sure, you can do Period over Period in other ways. The visual you want to eventually create (what fields are in it, are you showing just this week vs last week or all weeks vs their previous weeks, etc) is gong to determine how we approach this though.
Can you share a screenshot/mockup of the visual you eventually want to create?
I have added the pic of the table. you see delivery station in the first column and the other columns are:
1- # of sick DAs per station
2- WoW%
the date column is on top. it is the aggregated date to week. you can see I left the original formatting, i.e., DD MM YYYY. because it is aggregated you see only Sunday (which led to the problem in the first place).
WoW shows the variation on a weekly basis of the DA sickness. so in DOQ7 you had 1 sick DA in a week and 5 sick DAs in the next week, which gives us an increase of 400%.
I do have a column with week which I can use (it is numerical and has only the week number, so it is not a date column), but then I would need support to make the WoW calculation without the period over period function.
for information:
DA sick (SumOver ds) = sumOver(max({sum_sick_date}),[station,date, Country])
WoW% = periodOverPeriodPercentDifference({DA sick (SumOver ds)},date,WEEK,1)
Alternatively you can use the percentDifference() function (this is what the shortcut above is using behind the scenes). The ‘sort attribute’ can be any kind of field, doesn’t have to be a date (so long as the field sorts the way you want it to).