Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Every one,
I have 5 asset classes in my chart and periods are P0 to P1, out of 5 asset classes 1 is OTC Derivative, when I make selection of OTC and Period=P0 values should be added to other periods(P1 to P15) and you can find current chart and it should be change to my requirement.
Make sure that this change should not reflect to other selections.
Current expression is: if(Trad_PRODUCT_TYPE = 'OTC Derivative',sum(Trad_UNREALIZED_PL), if(Trad_PRODUCT_TYPE = 'SFT',sum(Trad_COLLATERAL_VALUE),sum(Trad_CURR_BOOK_VALUE)))
Thanks for your help in advance.
Raj
Did the above expression worked out for you?
Hi SunIndia,
Its not worked out.
I attached new and old expressions results.
Thank you.
its not working
hi
Could you able to provide sample data in excel with expected result..
Hi Mayil,
Added sample data.
Thank you
Hi
Try like this. Its one of the method to resolve ur problem..
Data:
LOAD Period,
Unr_PL,
Exc_Code,
Type
FROM
Sample.xlsx
(ooxml, embedded labels, table is Data);
Left Join
Load Exc_Code, Sum(Unr_PL) AS OTC Resident Data where Type = 'OTC'
Group by Exc_Code;
Front end:
Dimension: Period, Exc_Code
Measure: =If(GetFieldSelections(Type)= 'OTC', If(Type <> 'OTC',Sum({<Type = >}Unr_PL) + Max({<Type = >} OTC), Sum({<Type = >}Unr_PL)), Sum({<Type = >}Unr_PL) )
Your sample data is confusing. In your Excel file, I understood Cell K4 where you have added Unr_Pl for Type OTC, But for Cell K5 you have added Unr_Pl where Type Blank. Then in Cell K6 you have added Unr_Pl for Type SFT. There seems to be not consistency in the result you are expecting. Are you sure there are no mistakes in the expected result you posted in the Excel file?
Best,
S
Hi,
Below expression is almost worked but the problem is its calculating P0+P0 values also. My expectation is P0 values should be P0 only, P0 values should be added to other periods: P0+P1 like that.
Attaced screen shot.
Sum
(Trad_UNREALIZED_PL) + If( Period = 'P0', 0, RangeSum(Above(sum(Trad_UNREALIZED_PL) ,RowNo()-1)))
Thank you,
Hi Sunindia,
You are right and corrected now.
Hi Mayil,
I cant touch script part. Any other way.
TY