Help on expression

[ganeshreddy]

Hi All,

Please find attached QVW, how to get the correct current participation values in attached file, here current participation value should always be higher than market share, the values for current participation are incorrect in the present straight table.

I have tried the substringcount function instead of possible values but it didn't work.

Help will be much appreciated,

Thanks,

Ganesh

[sunny_talwar]

Where did you try the SubStringCount() function?

if(Column(1) <> 0 and TransType='T',

(Sum({<MarketName=>}  GrossWrittenPremium_GBP)-Sum( {<POLICYMARKET-=p(MarketName)>} [GrossWrittenPremium_GBP]))/Sum({<MarketName=>} GrossWrittenPremium_GBP),0)

[ganeshreddy]

Hi Sunny,

In the below underlined part i have tried substirngcount function like sum(substringcount(POLICYMARKET,MarketName)=0,GrossWrittenPremium_GBP),0)

Current expression:

if(Column(1) <> 0 and TransType='T',

(Sum({<MarketName=>}  GrossWrittenPremium_GBP)-Sum( {<POLICYMARKET-=p(MarketName)>} [GrossWrittenPremium_GBP]))/Sum({<MarketName=>} GrossWrittenPremium_GBP),0)

I was unable to compare to strings i.e. POLICYMARKET,MarketName to get market opportunity value. and also I am not sure whether i am using total money values in the current expression, please help me on this.

Thanks,

Ganesh

[sunny_talwar]

So, essentially the goal is to exclude those rows from the calculation where POLICYMARKET includes MarketName value? So, if anywhere in the pipe separated listed, a single MarketName appears, the row will be excluded?

[ganeshreddy]

yes sunny i.e. the goal.

[ganeshreddy]

Hi jyothish8807

Here you will find a detailed explanation regarding 1376367, I was unable to get answer for this due to time zone difference.

FYI, Current participation = (Total GWP – GWP of Opportunity for that market) / Total GWP .

the main required articulation is GWP of Opportunity for that market

Thanks,

Ganesh

[jyothish8807]

Hi Ganesh,

from your data it seems all the polymarket have the Market name with in it. In this case the output will be 100% always.

Try like this:

if(Column(1) <> 0 and TransType='T',

(Sum({<MarketName=>}  GrossWrittenPremium_GBP)-sum(if(index('|' & [POLICYMARKET] & '|', '|' & MarketName & '|') = 0, [GrossWrittenPremium_GBP])))/Sum({<MarketName=>} GrossWrittenPremium_GBP),0)

Br,

Jyothish KC

[ganeshreddy]

Hi Jyotish,

Thanks for your help on this, I did a slight change in provided expression. As we are calculating the opportunity value we need to calculate the sum of rows where the market name is not present in policy market string.

instead of equal to zero, i kept 1 in index function.

but the results are not matching the acceptance criteria, i.e. the current participation value should always be higher than market share, can you help me if anything needs to be changed here?

Thanks,

Ganesh

[jyothish8807]

Hi Ganesh,

You cannot set the value to '1'. In this case '0' means Poly market do not contain the Market Name and if the output is >0 then it means Poly market contain Market name.

In your data set i couldnt find a entry which full flll this condition all the market is present in Poly market field.

BR,

KC

[ganeshreddy]

Hi Jyothish,

Please correct me if I am going wrong. We have a first version of dashboard where we used to select particular market name to see the dashboard results, there the current participation expression working fine because it is given as below.

if(Column(1) <> 0 and TransType='T',

(Sum({<MarketName=>}  GrossWrittenPremium_GBP)-sum(if(substringcount([POLICYMARKET] , '$(vselectedMarket)') = 0, [GrossWrittenPremium_GBP]),0))/Sum({<MarketName=>} GrossWrittenPremium_GBP),0)

Where as in new requirement to compare the market peers, I need to give market name as dimension here and the above expression won't work here as we are not selecting any particular market name.  Is there any work abound that you can suggest to me to get the required report to compare the market peers.

Thanks,

Ganesh