topic Re: Chart Caluclation in QlikView

Chart Caluclation

Tue, 03 Feb 2015 11:38:42 GMT

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

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 11:43:43 GMT

I am sorry, but I don't really understand what you are trying to achieve here. When you make a selection on the Asset Class = OTC and Period =P0, what are you expecting to happen?

Best,

Re: Chart Caluclation

Tue, 03 Feb 2015 11:49:19 GMT

Hi S,

For ex: P0=10, P1=20,P2=5 then if select on OTC P0 value should be added to P1 to other periods.

Like our ex: after selection(OTC) P0=10 , P1=30(P0+P1), P2=15(P0+P2).

Thank you,

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 12:11:55 GMT

Is this close to what you want? PFA

Best,

Re: Chart Caluclation

Tue, 03 Feb 2015 12:23:16 GMT

Hi S, Not able to open your QVW due to license, could you please help me in another way. Thank you

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 13:03:34 GMT

Script for reference:

Table:

LOAD * INLINE [

Period, Value

P0, 10

P1, 15

P2, 20

P3, 5

P4, 40

P5, 54

P6, 25

P7, 20

P8, 30

P9, 32

P10, 10

P11, 15

P12, 40

P13, 43

P14, 23

P15, 10

];

Created a straight table with 2 expressions:

1) =Sum(Sales)

2) =Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Value, RowNo() - 1)))

Second expression will add P0 to everything from P1 to P15 (Snapshot attached)

Let me know if this is useful.

Best,

Re: Chart Caluclation

Tue, 03 Feb 2015 14:11:29 GMT

Hi S,

Thanks for the above expression and its meaningful but in my case, I am not able to understand for my below expression.

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

Thank you

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 14:21:26 GMT

I assume the value corresponds to one of these based on the selection (Trad_UNREALIZED_PL, Trad_COLLATERAL_VALUE, Trad_CURR_BOOK_VALUE)? If it does then you can do something like this:

If(Trad_PRODUCT_TYPE = 'OTC Derivative',

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_UNREALIZED_PL, RowNo() - 1))), If(Trad_PRODUCT_TYPE = 'SFT',

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_COLLATERAL_VALUE, RowNo() - 1))),

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_CURR_BOOK_VALUE, RowNo() - 1)))))

Is this something you are looking to do? If not then I would suggest that you share some sample data so that the actual requirement can be pin-pointed.

Best,

Re: Chart Caluclation

Tue, 03 Feb 2015 15:49:32 GMT

Hi S,

Thanks for the reply.

Data is huge so I can explain with screen-shot.

I have two dimensions 1. Period, 2. Trad_Exclusion_Rule(Dragged to up: 0,100,150...,). When I make selection(OTC) then P0 should be add to other periods(p1+p0,p2+p0...,). If I make selection of other Asset class and it should not affect.

my expression as above.

Your expression is not worked out:

If(Trad_PRODUCT_TYPE = 'OTC Derivative',

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_UNREALIZED_PL, RowNo() - 1))), If(Trad_PRODUCT_TYPE = 'SFT',

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_COLLATERAL_VALUE, RowNo() - 1))),

Sum(Value) + If(Period = 'P0', 0, RangeSum(Above(Trad_CURR_BOOK_VALUE, RowNo() - 1)))))

Thank you

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 16:07:52 GMT

If(Trad_PRODUCT_TYPE = 'OTC Derivative',

Sum(Trad_UNREALIZED_PL) + If(Period = 'P0', 0, RangeSum(Above(Trad_UNREALIZED_PL, RowNo() - 1))), If(Trad_PRODUCT_TYPE = 'SFT',

Sum(Trad_COLLATERAL_VALUE),

Sum(Trad_CURR_BOOK_VALUE)))

See if this one is working.

Best,

Re: Chart Caluclation

sunny_talwar — Tue, 03 Feb 2015 19:34:22 GMT

Did the above expression worked out for you?

Re: Chart Caluclation

Wed, 04 Feb 2015 08:36:50 GMT

Hi SunIndia,

Its not worked out.

I attached new and old expressions results.

Thank you.

Re: Chart Caluclation

Wed, 04 Feb 2015 09:43:05 GMT

its not working

Re: Chart Caluclation

MayilVahanan — Wed, 04 Feb 2015 10:04:13 GMT

Could you able to provide sample data in excel with expected result..

Re: Chart Caluclation

Wed, 04 Feb 2015 10:41:48 GMT

Hi Mayil,

Added sample data.

Thank you

Re: Chart Caluclation

MayilVahanan — Wed, 04 Feb 2015 11:27:15 GMT

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

Re: Chart Caluclation

sunny_talwar — Wed, 04 Feb 2015 11:27:43 GMT

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,

Re: Chart Caluclation

Wed, 04 Feb 2015 11:33:48 GMT

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,

Re: Chart Caluclation

Wed, 04 Feb 2015 11:44:55 GMT

Hi Sunindia,

You are right and corrected now.

Re: Chart Caluclation

Wed, 04 Feb 2015 11:46:40 GMT

Hi Mayil,

I cant touch script part. Any other way.