topic Re: Value adding to other values in QlikView

Value adding to other values

Fri, 06 Feb 2015 10:46:58 GMT

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)))

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

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.

Re: Value adding to other values

rubenmarin — Fri, 06 Feb 2015 10:54:34 GMT

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.

Re: Value adding to other values

avinashelite — Fri, 06 Feb 2015 10:59:39 GMT

initialize a variable for sum of P0 and try like this

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

vP0 is the variable

Re: Value adding to other values

Anonymous — Fri, 06 Feb 2015 11:05:59 GMT

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.

Re: Value adding to other values

Michiel_QV_Fan — Fri, 06 Feb 2015 11:09:42 GMT

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

Re: Value adding to other values

sunny_talwar — Fri, 06 Feb 2015 11:52:08 GMT

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,

Re: Value adding to other values

Fri, 06 Feb 2015 12:29:32 GMT

Hi Guys,

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

(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