
1. Re: Weight Average Calculation
Jim Wahl May 16, 2013 5:40 AM (in response to Leticia Soroa)Hi Leticia,
How about using WINDOW_SUM?
WINDOW_SUM([JTZ*WeightApplied])/WINDOW_SUM(SUM([JumpToZero$]))
If you select Edit Table Calculation for this pill, you'll see it's a nested table calc. with a dropdown menu at the top of the dialog box. For this new field, select Advanced compute using and set the Addressing fields to Year of Expiry, Quarter of Expiry. Then it should sum everything in the JTZ columns.
Your final layout / view isn't exactly clear to me, but perhaps Compute Using for MinExpiryDate could be set with the same addressing (you also have riskdate in the addressing column, which I'm not sure you need)?
Jim

test_jimw.twbx.zip 895.7 KB


2. Re: Weight Average Calculation
Leticia Soroa May 16, 2013 6:31 AM (in response to Jim Wahl)Hi Jim,
That is great!!! the value seems to be correct.
I still have a couple of questions though
 Where do you see the nested table calc? I don't seem to find it.
 The data is now correct but let's say I want to have issuer5YearSpread and The JTZ_WeightedAverage displayed on a graph over a period of time( expiry date) is that possible??.
Regarding RiskDate  I use this field to be able to know my minimum expiry date for the day I am looking at.
Thanks again!

3. Re: Weight Average Calculation
Jim Wahl May 16, 2013 7:50 AM (in response to Leticia Soroa)Where do you see the nested table calc? I don't seem to find it.
Rightclick on the new calculated field: JTZ_Weighted Average > Edit Table Calculation. At the top of the Table Calculation dialog box, you'll see a pulldown next to Calculated Field.
The data is now correct but let's say I want to have issuer5YearSpread and The JTZ_WeightedAverage displayed on a graph over a period of time( expiry date) is that possible??.
I don't quite understand the data or the exact view you're after, but I don't see why you couldn't have both measures on the same chart (with dualaxis). For example, you could take Sheet 2 in the example I posted and move Year from the detail shelf to Columns, move JTZ_WeighedAvg from Measures to Rows, and move Name from Rows to Colors. And you should have a line plot of this value. And now you can add another measure, such as Issuer5YearSpread, to the Rows shelf. This will give you two graphs, which can be consolidated by selecting dualaxis.
Also, I don't think this is what you want, but you can change the partitioning of JTZ_WeightedAverage so that it's computed every year rather than covering all years by editing the table calc and either 1) moving Year back to the lefthand side for partitioning or 2) set "reset every" to Year. The only difference is that (2) allows you to maintain a sort order, but since your table calcs are not dependent on sort order, (1) and (2) should be equivalent. ...
Jim

4. Re: Weight Average Calculation
Leticia Soroa May 16, 2013 8:04 AM (in response to Jim Wahl)Sorry Jim! completely my mistake! I hadn't seen the sheet 2!!
It is great!! thanks a lot! I need to go through it but this is what I was looking for.
Thanks again!! and sorry for not seeing all the workbook!

5. Re: Weight Average Calculation
Jim Wahl May 16, 2013 8:08 AM (in response to Leticia Soroa)No problem. Glad to help. ...
Jim

6. Re: Weight Average Calculation
Leticia Soroa May 16, 2013 8:34 AM (in response to Jim Wahl)Hi Jim,
One last question If you look at the minimum expiry date in the TEST tab the value does not change when you change risk date. Is there any way to do this?
It should really do MIN expiry date for that specific RiskDate.
Thanks!

7. Re: Weight Average Calculation
Jim Wahl May 17, 2013 1:51 AM (in response to Leticia Soroa)It should really do MIN expiry date for that specific RiskDate.
To do this you need to partition by RiskDate in the table calc.
Currently you have the MinExpiryDate pill setup with advanced compute using. So you’d go into the table calc dialog box and move risk date from the addressing side to the partitioning side. You can also choose the option reset every RiskDate. The main reason for the latter is that RiskDate can then be used to sort. In your case, WINDOW_MIN() doesn’t require a sort order, so the result is the same.
Now your table calc is addressing only on ExpiryDate, partitioned by everything else, which in this case in Name and RiskDate. You might notice that you don’t really need to use the advanced compute using option. You can simply select ExpiryDate in the table calc pill to get the same result. This makes it easier to doublecheck the table calcs in the view.
This fixes your MinExpiryDate issue, but there’s still a problem with YearWeight calculation. If you set riskdate to 20120202, the MinExpiryDate is 20120215 and, therefore, YearWeight should be 0, but it’s 2. What’s happening is that you want YearWeight to be partitioned on MinExpiryDate, but you can’t partition on a table calc field. I fixed this by including the formula for MinExpiryDate in YearWeight:
DATEDIFF( "year", WINDOW_MIN(MIN([ExpiryDate])), ATTR([ExpiryDate]))
I also changed the last part of the formula from a MIN() to ATTR(), since you’re computing this value at the level of detail where there should only be one ExpiryDate value. In this case, MIN(), MAX(), and ATTR() will all result in the same value, but ATTR() has the advantage of returning * if there are multiple values and this provides some level of error catching.
Check out the attached. The new calculations have the (new) suffix. Again, I'm not entirely sure I understand your data set and final display goals, so perhaps some of the above changes still need a bit of work.
Jim

test_v2_jimw.twbx.zip 921.8 KB


8. Re: Weight Average Calculation
Leticia Soroa May 17, 2013 2:16 AM (in response to Jim Wahl)Hi Jim,
This is extremely useful stuff!!
Thanks a lot for your help on this!
Leticia