Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Time intelligence related issue

Hello Community,

Greetings!

 

I am running into some serious issue, I have a data set which has columns shipstart, shipend and profit amount, 
1) Based on this data set I wnat to calculate if date is in between 1/8/2022 and 31/7/2023 I want sum of Profit amount this is easy part.
2) Tricky part is, if my shipend date is after 31/7/2023, First I have to calculate number of months between shipstart and shipend date than divide profit amount by total month .
3) Calculate total months between shipstart date and 31/7/2023 than multiply it with profit amount which I got from point 2.
4) The total should show combination of all 3 points.

Ship startShip endApproved offers $  Total months from shipstart to 7/31/2023Total number of monthsProfit amount/Total number of monthH8*F8
8/1/2022 0:008/31/2022 0:00$22,476$22,476 
8/1/2022 0:009/1/2022 0:00$3,892$3,892 
8/1/2022 0:009/29/2022 0:00$2,788$2,788 
8/1/2022 0:0012/31/2022 0:00$22,653$22,653 
8/1/2022 0:003/1/2023 0:00$18,488$18,488 
8/1/2022 0:007/31/2023 0:00$205,340$205,340 
8/1/2022 0:009/1/2023 0:00$3,644$3,123.43 1214$260.29$3,123.43
8/2/2022 0:003/31/2022 0:00$22,668$22,668     
8/2/2022 0:0012/30/2022 0:00$31,805$31,805     
8/2/2022 0:0012/31/2022 0:00$16,899$16,899     


I hope you got my question and help me to resolve this issue.

Thanks in advance for your help and time!

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Maybe:

    Column = 
  VAR __ShipStart = [Ship start]
  VAR __ShipEndDate = [Ship end]
  VAR __Offer = [Approved offers $]
  VAR __TotalMonths = DATEDIFF( __ShipStart, __ShipEnd, MONTH ) + 1
  VAR __TotsMonths = DATEDIFF( __ShipStart, DATE( 2023, 7, 31 ) + 1
  VAR __Result = DIVIDE( __Offer, __TotalMonths ) * __TotsMonths
RETURN
  __Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Maybe:

    Column = 
  VAR __ShipStart = [Ship start]
  VAR __ShipEndDate = [Ship end]
  VAR __Offer = [Approved offers $]
  VAR __TotalMonths = DATEDIFF( __ShipStart, __ShipEnd, MONTH ) + 1
  VAR __TotsMonths = DATEDIFF( __ShipStart, DATE( 2023, 7, 31 ) + 1
  VAR __Result = DIVIDE( __Offer, __TotalMonths ) * __TotsMonths
RETURN
  __Result