Forum Discussion
question regarding dates
Hi bolabuga,
>>Did i understand it right??
Yes, it is my option.
>>2- Again considering i understood correctly, theres a problem. Depending on the cycle, it can start at 11 and ending at day 9 or start at day 13 and end at day 11. I will write the actual 2016 cycle dates when i have a time window here on job.
It is hard to calculate the cycle range if you use the random days range to calculate it. I can calculate the cycle if you provide a specific range of days and an start date.
For example: 30 day, start date 2015/1/11.
Measures:
StartDate = DATE(2015,1,11)
DayRange = 30
Current Cycle =
var currentDate=MAX([Date])
var cycleCount=DATEDIFF([StartDate],currentDate,DAY)/[DayRange]
var cycleTotal=INT(cycleCount)+ if(MOD(DATEDIFF([StartDate],currentDate,DAY),[DayRange])>0,1,0)
return
if(currentDate>=[StartDate],if(MOD(cycleTotal,12)>0,cycleTotal-INT(cycleTotal/12)*12,if(MOD(cycleTotal,12)=0,12,cycleTotal)),-1)Regards,
Xiaoxin Sheng
Hi bolabuga,
You can create calculated columns to calculate cycle and cycle year(as same as fiscal year) :
Sample:
Calculate columns:
Cycle = if([Date]>=DATE(YEAR([Date]),MONTH([Date]), 11),MONTH([Date]),if([Date]<DATE(YEAR([Date]),MONTH([Date]), 11),IF(MONTH([Date])>1,MONTH([Date])-1,12),BLANK()))
Cycle Year = if(MONTH([Date])=1&&DAY([Date])<11,YEAR([Date])-1,YEAR([Date]))
Visual:
Regards,
Xiaoxin Sheng
Hello xiaoxin, thks for the reply.
I did some writes on paper testing some dates using the calculated column you proposed. What i understand is.
1- The "calculate columns" are always considering that my cycle starts at day 11 and end at day 10, and getting "cycle" and "cycle year" numbers based on that range each month. Did i understand it right??
2- Again considering i understood correctly, theres a problem. Depending on the cycle, it can start at 11 and ending at day 9 or start at day 13 and end at day 11. I will write the actual 2016 cycle dates when i have a time window here on job.
adding new excel sample, sorry i should have added this the first time.
Hi bolabuga,
>>Did i understand it right??
Yes, it is my option.
>>2- Again considering i understood correctly, theres a problem. Depending on the cycle, it can start at 11 and ending at day 9 or start at day 13 and end at day 11. I will write the actual 2016 cycle dates when i have a time window here on job.
It is hard to calculate the cycle range if you use the random days range to calculate it. I can calculate the cycle if you provide a specific range of days and an start date.
For example: 30 day, start date 2015/1/11.
Measures:
StartDate = DATE(2015,1,11)
DayRange = 30
Current Cycle =
var currentDate=MAX([Date])
var cycleCount=DATEDIFF([StartDate],currentDate,DAY)/[DayRange]
var cycleTotal=INT(cycleCount)+ if(MOD(DATEDIFF([StartDate],currentDate,DAY),[DayRange])>0,1,0)
return
if(currentDate>=[StartDate],if(MOD(cycleTotal,12)>0,cycleTotal-INT(cycleTotal/12)*12,if(MOD(cycleTotal,12)=0,12,cycleTotal)),-1)Regards,
Xiaoxin Sheng
thks xiaoxin, although i cant apply the solutions to my current scenario, it will surely help me along the way. Nice suggestions.
