Use if function in Sum in expression

[#1]

In a table i have the next values (among others of course),

Year, StartWeek, EndWeek

I want to calc the period in weeks between the start and and week. I have the next formule which works fine for records with the start en the end week in the same year.

SUM({<Source={Source1},Year={$ (=maxstring(Year))}>}[EndWeek] -[StartWeek]+1)
This one works fine.

When the start week is in one year and the end week in the other i get the next, asuming that one year is always 52 weeks

SUM({<Source={Source1},Year={$ (=maxstring(Year))}>}
If([EndWeek]-[StartWeek]<<0,
52-[StartWeek]+[EndWeek]+1,
[EndWeek]-[StartWeek]+1
)
)

But this one returns strange high values.

Can anyone give me a help please? Thnx in advance

[#2]

I'm not 100% sure what the solution is but the problem is that your week numbers don't have years associated to them (the first week of every year is 1 instead of 1 and 53, etc.). I think you need to use the weekyear function on for the [StartWeek] and [EndWeek] fields in the script.

[#3]

This could be a solution, but I also want to know how to solve it with the if function as I described

[#4]

Ignore my last suggestion I was confused by the reference manual. I'm pretty sure that's still what you want to do but I'm not sure which function to use or anything like that.

[#5]

Someone else a hint, I'm a bit stuck with this problem. Thnx in advance !

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

My guess is you have "Week" as dimension (field) with possible values from 1 to 52 (or 53). When the chart displays more than one year -say 2010 and 2011-, it accumulates the Week number, meaning that when you see at Week 2 the chart is aggregating values from week 2 in year 2011 and year 2010. So I'd create a new field in your master calendar like

WeekName(YourDateField) AS WeekName

Then the expressión should work without conditional, using this new field as dimension, instead of "Week".

Hope that helps.

[#7]

Hello,

I am sure that you can't use your expression in the way you do. According to what Miguel said: If you do not differentiate your weeks (belonging to exactly one year) it won't work correctly. Examp:

- the result of your expression is for "05-2010" and "06-2011" the same as for "05-2010" and "06-2010". First should be 53!!

Regards, Roland