Rebecca Roland Posted December 5, 2023 Share Posted December 5, 2023 Examples3 days = 0.3 weeks10 days = 1.3 weeks7 days = 1 week5 days = 0.5 week14 days = 2 weeks Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 So you do not want to say that 10 days is 10/7=1.42 weeks, but you want the outcome to appear as a string of: number of weeks.number of daysis this correct?In this case, try this. It would produce a string (you can cast into a Real if you want a number):Concatenate(Integer(Floor([CounfOfDays] / 7)),'.',Integer(Mod([CounfOfDays],7))) Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 5, 2023 Author Share Posted December 5, 2023 Hi @Gaia Paolini thank you for replying so quickly. I have a dataset with number of days that a member was away, I need to create a parameter to say if away for more than 8 weeks then yellow, less than 5 weeks then orange and so on. I was hoping to convert the day count to weeks to make that calculation better to facilitate this:case when DateDiff('week',[date_column],Today()) <= 5 then 'Orange' when DateDiff('week',[date_column],Today()) <= 8 then 'Yellow' when DateDiff('week',[date_column],Today()) > 8 then 'Green' when [date_column] is null then 'Red' end--------------------------------User A off for 10 daysUser B off for 3 days that calculation would replace the 'date column' in the colour parameter. Hope I am making sense Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 Do you have the number of days the member was away already in a column (the one I called [CountOfDays] in my previous answer)? In that case you could just express the CASE this way: case when [CounfOfDays]<=5 then 'Orange' when [CounfOfDays]<=8 then 'Yellow' when [CounfOfDays]>8 then 'Green' when [CounfOfDays] is null then 'Red' end In this case, you would not really need to calculate the weeks in the way you asked, a simple division by 7 would be good enough. Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 5, 2023 Author Share Posted December 5, 2023 @Gaia Paolini yes, let me do that and try. Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 5, 2023 Author Share Posted December 5, 2023 Oh but the 5, 8 and 8 is number of weeks so I would need to change them to days. So 5 weeks is 35 days case when [CounfOfDays] <= 35 then 'Orange' when [CounfOfDays] <= 56 then 'Yellow' when [CounfOfDays] > 56 then 'Green' when [CounfOfDays] is null then 'Red' end Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 Yes, sorry that was expressed in days, but you should be ok by simply expressing the CASE statement like this: where [CountOfDays]/7 is effectively your count of weeks case when [CounfOfDays]/7 <= 5 then 'Orange' when [CounfOfDays] /7<= 8 then 'Yellow' when [CounfOfDays]/7 > 8 then 'Green' when [CounfOfDays] is null then 'Red' end Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 5, 2023 Author Share Posted December 5, 2023 It returns an error :-/ It says 'invalid type for function call '/'' Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 have you tried surrounding it with ()?([CounfOfDays] / 7) Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 also you might need to check the order of the when clauses, or make them more explicit e.g.when ([CounfOfDays] /7)>5 and ( [CounfOfDays] /7)<= 8 then 'Yellow' Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 5, 2023 Author Share Posted December 5, 2023 Yes, I wrapped the calcs, still same error. They are never helpful these errors, thank you for suggesting things. casewhen ([Count of Days Calc]/7) <= 5 then 'Orange'when ([Count of Days Calc]/7) <= 8 then 'Yellow'when ([Count of Days Calc]/7) > 8 then 'Green'when ([Count of Days Calc]) is null then 'Red'end Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 5, 2023 Share Posted December 5, 2023 can you check what the data type of CountOfDays is? Link to comment Share on other sites More sharing options...
Rebecca Roland Posted December 7, 2023 Author Share Posted December 7, 2023 Morning, sorry I had AL. Back today! This is the CountOfDays calc Concatenate(Integer(Floor([duration_days] / 7)),'.',Integer(Mod([duration_days],7))) Link to comment Share on other sites More sharing options...
Gaia Paolini Posted December 7, 2023 Share Posted December 7, 2023 That formula with Concatenate was to reply to your initial question, and it produces a string. In order for it to produce a real number, you need to put Real(..) around it. However, there is no need to calculate the count of days this way, that would be only for display purposes, it is not meaningful as a number. To perform the comparison, you just need the duration in days divided by 7. It will give you a number of weeks as a real number, which can be compared to 5, 8 etc.So if you have a [duration_days] column, and this is a number, my suggestion is to divide it by 7 to find out the count of weeks.Then use the case when... etc formula as:casewhen ([duration_days]/7) <= 5 then 'Orange'when ([duration_days]/7) >5 and ([duration_days]/7) <= 8 then 'Yellow'when ([[duration_days]/7) > 8 then 'Green'else 'Red'endThis will give you 'Orange' when it is less than 5 weeks, 'Yellow' when it is between 5 and 8 weeks, and 'Green' when it is over 8 weeks. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now