36 Replies Latest reply: Feb 6, 2015 6:52 AM by Sunny Talwar

# Chart Caluclation

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.

Raj

• ###### Re: Chart Caluclation

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,

S

• ###### Re: Chart Caluclation

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

Is this close to what you want? PFA

Best,

S

• ###### Re: Chart Caluclation

Script for reference:

Table:

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,

S

• ###### Re: Chart Caluclation

Hi S,

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

Thank you

• ###### Re: Chart Caluclation

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:

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,

S

• ###### Re: Chart Caluclation

Hi S,

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:

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

See if this one is working.

Best,

S

• ###### Re: Chart Caluclation

Did the above expression worked out for you?

• ###### Re: Chart Caluclation

Hi SunIndia,

Its not worked out.

I attached new and old expressions results.

Thank you.

• ###### Re: Chart Caluclation

its not working

• ###### Re: Chart Caluclation

hi

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

• ###### Re: Chart Caluclation

Hi Mayil,

Thank you

• ###### Re: Chart Caluclation

Hi

Try like this. Its one of the method to resolve ur problem..

Data:

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

Hi Mayil,

I cant touch script part. Any other way.

TY

• ###### Re: Chart Caluclation

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

• ###### Re: Chart Caluclation

Hi Sunindia,

You are right and corrected now.

• ###### Re: Chart Caluclation

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

Thank you,

• ###### Re: Chart Caluclation

or

Can you try and see if one of the two are working for you

Best,

S

• ###### Re: Chart Caluclation

Can you take a screen shot of the expression which is doing P0+P0? I know you did post what you are using, but I just want to make sure everything is correct in the expression box.

Thanks,

S

• ###### Re: Chart Caluclation

Hi S,

Its not worked out:

almost worked:

Sum

()-1)))

but only thing P0 should not be calculte.

• ###### Re: Chart Caluclation

Can you create two separate expressions for testing purposes:

2) If( Period = 'P0',0, RangeSum(Above(sum(Trad_UNREALIZED_PL) ,RowNo()-1)))

Paste the image of the output chart.

Best,

S

• ###### Re: Chart Caluclation

Replace the first expression with this:

If(Period = 'P0', 0, Sum (Trad_UNREALIZED_PL))

If this expression now gives you 0 for 'P0' then you can use this as your final expression

Hope we can final resolve your issue here.

Best,

S

• ###### Re: Chart Caluclation

Sorry SunIndia, Still its not working. I really thankful for trying hard.

• ###### Re: Chart Caluclation

Hahahaha not an issue. I am going to give it a one last try, if I can't figure out, than I guess I can't really be able to.

I want you to create these expressions and paste the image:

1) If(Period = 'P0', 0, Sum (Trad_UNREALIZED_PL))

2) If( Period = 'P0',0, RangeSum(Above(sum(Trad_UNREALIZED_PL) ,RowNo()-1)))

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

Thanks,

S

• ###### Re: Chart Caluclation

I will try tomorrow.

• ###### Re: Chart Caluclation

Not a problem.

Best,

S

• ###### Re: Chart Caluclation

Thanks Sunindia,

finally I got it, really thank you very much. On first incident itself, you found the solution but from my side made some mistake.

Thank you.

• ###### Re: Chart Caluclation

I am glad that finally we were able to get to the solution.

Best,

S

• ###### Re: Chart Caluclation

Hi Sunindia,

http://community.qlik.com/message/708790#708790

Thank you

• ###### Re: Chart Caluclation

I will look at it in a bit. Thanks for reaching out.

Best,

S