Logic Help in opportunity caliculaton?

[ganeshreddy]

Hi All,

I have two sheets in attached QVW, In the first sheet view/report all metrics are giving us the correct results, where as in second sheet/ report the highlighted Total GWP of opportunity and current participation values are incorrect due to used expressions, PFB used expression.

Sum({<TransType={'T'}, MarketName=>} if(substringcount(POLICYMARKET, '$(vSelectedMarket)')=0, [GrossWrittenPremium_GBP], 0))

According to requirement in the place of used variable 'vSelectedMarket' we need to pass respective dimension value for each row in the report, and I am unable to pass that respective dimension value.

can any one suggest me the correct expressions or help me to create logic in script level?

Thanks,

Ganesh

[tresesco]

A smaller sample would attract more replies I guess.

[tresesco]

What if you put the dimension expression directly in the expression like:

Sum({<TransType={'T'}, MarketName=>} if(substringcount(POLICYMARKET, right(BusinessType,4))=0, [GrossWrittenPremium_GBP], 0))

[ganeshreddy]

Hi Tresesco,

PFA the smaller sample, The first report in shhet1 is working fine and providing the correct results, the actual problem is in sheet2 report where we have market name as dimension and here the provided expression is not working and giving us Total GWP value as zero. PFB and suggest me to achieve the required output..

[tresesco]

Use the following expression:

if($(vShowColumn) = 0, 0,

Sum({<TransType={'T'},MarketName=>} [GrossWrittenPremium_GBP])

)

[ganeshreddy]

Hi Tresesco,

We can not use this provided expression because, need to calculate Total GWP of Opportunity in the respective column and not the GWP .PFB

Total GWP of Opportunity = The Total GWP which the market has not had any participation in.

Best Regards,

Ganesh

[tresesco]

Could you share expected output numbers (at least for few rows)?