Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
ganeshreddy
Contributor III

Help on expression

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

15 Replies
Highlighted

Re: Help on expression

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)

Highlighted
ganeshreddy
Contributor III

Re: Help on expression

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

Highlighted

Re: Help on expression

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?

Highlighted
ganeshreddy
Contributor III

Re: Help on expression

yes sunny i.e. the goal.

Highlighted
ganeshreddy
Contributor III

Re: Help on expression

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

Highlighted
jyothish8807
Honored Contributor II

Re: Help on expression

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

Best Regards,
KC
Highlighted
ganeshreddy
Contributor III

Re: Help on expression

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

Highlighted
jyothish8807
Honored Contributor II

Re: Help on expression

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

Best Regards,
KC
Highlighted
ganeshreddy
Contributor III

Re: Help on expression

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