Value adding to other values

[#1]

Hi Every one,

I want to add value P0 value to other periods(P1 to P2) and P0 value should be the same.

Current Expr:

if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))

Dim1	Dim2	Dim3	Period:	P0	P1	P2	P3
				100	10	55	50
				25	19	88	44
				10	100	85	33
				20	28	25	46
				12	30	56	58

Now I am looking like below, when I make selection of Product type= OTC, then

Dim1	Dim2	Dim3	Period:	P0	P1	P2	P3
				100	110(P0+P1)	155(P0+P2)	150(P0+P3)
				25	44(P0+P1)	113(P0+P2	69(P0+P3)
				10	110(P0+P1	95(P0+P2)	43(P0+P3)
				20	48(P0+P1	45(P0+P2)	66(P0+P3)
				12	42(P0+P1	68(P0+P2)	70(P0+P3)

Please check the screen shot of the current chart.

Thank you.

[rubenmarin1]

Hi Raj, you can use:

if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))

+ if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(TOTAL <Dim1, Dim2, Dim3> {<Period={'P0'}>} Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(TOTAL <Dim1, Dim2, Dim3> {<Period={'P0'}>}Trad_COLLATERAL_VALUE),sum(TOTAL <Dim1, Dim2, Dim3> {<Period={'P0'}>}Trad_CURR_BOOK_VALUE)))

Or:

if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))

+ If(Period<>'P0', First(if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE))))

Not tested, hope this helps.

[avinashelite]

initialize a variable for sum of P0 and try like this

if(Product='P0',sum(AMOUNT),sum(AMOUNT)+$(vP0))

vP0 is the variable

[Anonymous]

Hi,

You can simply use for

P0: your expression

P1 : column(1)+ expression of your P1

P2: column(1)+ expression of your P2

etc....

The function column(x) returns the value of the expression in the column x, for example the first expression is column(1), the second expression is column(2)...

Hope it helps you.

[Michiel_QV_Fan]

Create an additional column in your fact table by loading the P0 values from that same fact table with the needed key fields. Generate a surrogate key .....

Then left join P0 back to the fact table in a new column.

You can make a sum for the P1 and forward by creating an expression:

<value>+P0 and display that in a pivot chart

[sunny_talwar]

Please try the below expression:

=Sum(Value) + If(Period = 'P0', 0, Aggr(Above(Sum(Value), RowNo() -1), Dim1, Dim2, Dim3, Period))

Replace Value with Trad_UNREALIZED_PL (your field name)

Best,

S

[#7 · Accepted solution]

Hi Guys,

With Ruben's and Avinash's idea, I could make it possible with my scenario like this.

if

(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))+
if(Trad_PERIOD='P0',0,sum(TOTAL <Trad_CRISP_PARENT_FULLNAME, Trad_PARENT, Trad_CLOSEOUT_AGREEMENT_NUMBER> {<Trad_PERIOD={'P0'}>} Trad_UNREALIZED_PL))

Thank you all