Calculation context in crosstab webi

[former_member222155]

screen1.jpgHi Gurus,

I have a cross tab with "As of Date" on top horizontal row(6/13, 9/7, 9/14) and "Rate date" on vertical left most col. and the body has Interpolated "Frwd rate" formula used is =(Interpolation([Fwd Rate])) results attached in screen1. The orange values are the interpolated values.

But the requirement is marked/highlighted values should be '0' or 'N/a' (no interpolation should happen) for "rate date" less than 9/8/2017 for "as of date" 9/7/2017 and same for

"rate date" less than 9/15/2017 for "as of date" 9/14/2017 as shown in screen2screen2.jpg

[amitrathi239]

try this.

=if([Fwd Rate]<>(Interpolation([Fwd Rate]))) then 0 else Interpolation([Fwd Rate])

[former_member182342]

Hi Mahendra,

Is date is going to be constant in the above logic ?

if it is remain to be constant then go for alerter and use your condition there else please share the logic for dynamic date change.

Regards,

Anish

[former_member222155]

Thanks for your response Anish!!

yes dates is const & both the dates are direct fields. But user can select any number of "As of Date"s and for each "As of Date" the "Rate Date" s will vary. So requirement is to interpolate those gaps for "Rate date"s where there is no value for particular "as of date" (but value exist for other "rate date" & "as of date" combination value exists) Interpolated values are shown in orange color.

pls let me know if any inputs needed