Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
initialize a variable for sum of P0 and try like this
if(Product='P0',sum(AMOUNT),sum(AMOUNT)+$(vP0))
vP0 is the variable
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.
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
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
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