What formula to distribute a value of between two dates with a breakdown by annual quarter

[aymeric boutan]

Hello

I have this input data colume A, B & C

 

 

 

A

B

C

 

 

TBF (en h)

Dbut calcul

Fin calcule

 

 

600

02/08/21

28/12/21

 

 

 

and i need to create 8 new columm .

Goal is todistribute a value (A) of between two dates (B&C) with a breakdown by annual quarter (New colum 1 to 8)

 

 

 

1

2

3

4

5

6

7

8

 

 

 

T1 2021

T2 2021

T3 2021

T4 2021

T1 2022

T2 2022

T3 2022

T4 2022

 

 

 

0

0

242

358

0

0

0

0

 

 

 

 

How can i dot this in spotfire

Thanks for your Help

[Andreas Laestadius]

Hi,

You may use the Spotfire custom expresion language to extract the DatePart, eg the year and quarter

Pseud code:

DatePart("qq",Arg2) + " " + DatePart("yy",Arg2)

Pseudoresult:

"2 2021", "42022" etc

You can then use pivoting and unpivoting to first place your time data in a single column, then transfer them to be columns.

You cannot however automagically split 600 into 242 and 358 using spotfire built in functions, you will have to craft a way for that.

Hope this helps,

Andreas