topic Re: weeknum function bad results in Dashboards & Analytics Discussions

weeknum function bad results

Random_1191 — Fri, 02 Jun 2023 05:55:04 GMT

Hi there,

Im using weeknum function but it seems that results at not correct eg below. Column manual shows proper CW from the week numbers table 2021. First row 5 november 2021 - CW44 but according to function CW45 ... why ?

Next question why when there is now value in cell function always shows CW 34 ? instead of empty values ?

Now, I've tried to use IF function to solve it but function I've made :

if(string(Sch_Active.Date)="","",weeknum(Sch_Active.Date))

shows an error to column Sch_Active.Date

Any clues , help ?

Re: weeknum function bad results

JoeM — Fri, 02 Jun 2023 15:56:15 GMT

@Random_1191 - I think you are using https://www.epochconverter.com/weeks/2021 as your reference? If so, I think the difference is that week 01 starts on January 4th, and January 1-3 days are not counted as a week in 2021 years. In Incorta weeks are counted on each Sunday, so Incorta counts week 1 as Jan 1-2 (Friday & Saturday) and starts week 2 on Jan 3. The way Incorta calculates weeks would be consistent with Excel.

As for the null values - quite interesting! I'll have to relay this to the product team. In the meantime, this formula should force the empty value:

case( isNull( DateWeek.Sheet1.Date ), "", string( weeknum( DateWeek.Sheet1.Date ) ) )

Re: weeknum function bad results

Random_1191 — Mon, 05 Jun 2023 05:53:25 GMT

@JoeM thanks for info. Formula You build works thx.

Correct me if I'm wrong, from what I understand the Incorta weeknum counts week starting Sunday to Saturday? as the weeks from the https://www.epochconverter.com/weeks/2021 are counting Monday to Saturday.

When it comes to the weekly formula, Incorta counts one week ahead, but in the following cases it counts even two weeks ahead, which surprises me. Why ?

Would it be possible to adjust formula for selecting the start-end, or add additional weeknum for monday-sunday ?