topic Re: Help on expression in QlikView

Help on expression

ganeshreddy — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help on expression

sunny_talwar — Mon, 06 Nov 2017 14:22:26 GMT

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)

Re: Help on expression

ganeshreddy — Mon, 06 Nov 2017 14:28:32 GMT

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

Re: Help on expression

sunny_talwar — Mon, 06 Nov 2017 14:42:23 GMT

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?

Re: Help on expression

ganeshreddy — Tue, 07 Nov 2017 02:36:22 GMT

yes sunny i.e. the goal.

Re: Help on expression

ganeshreddy — Tue, 07 Nov 2017 07:36:23 GMT

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

Re: Help on expression

jyothish8807 — Tue, 07 Nov 2017 08:11:41 GMT

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

Re: Help on expression

ganeshreddy — Tue, 07 Nov 2017 10:21:11 GMT

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

Re: Help on expression

jyothish8807 — Tue, 07 Nov 2017 10:37:25 GMT

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,

Re: Help on expression

ganeshreddy — Tue, 07 Nov 2017 11:59:14 GMT

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

Re: Help on expression

ganeshreddy — Tue, 07 Nov 2017 14:58:13 GMT

Hi stalwar1‌ ,

Please help me on this, was unable get knowledge from you yesterday.

Thanks,

Ganesh

Re: Help on expression

sunny_talwar — Tue, 07 Nov 2017 15:09:50 GMT

Lets break it down to this

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

What is the expected output here

Re: Help on expression

ganeshreddy — Wed, 08 Nov 2017 03:54:39 GMT

Hi Sunny,

That has to be the market opportunity value i.e.

Mock exp:

Sum( {<POLICYMARKET<>MarketName)>} [GrossWrittenPremium_GBP])

FYI exclude the matched values of POLICYMARKET string and MarketName values in the summation.

ex : POLICYMARKET= Abu|Aca|Adv (0r) Aca|Adv|Abu|Abu

MarketName = Abu

Exclude the MarkrtName value matched rows to POLICYMARKET strings and provide total GWP_GBP opportunity value.

Thanks,

Ganesh

Re: Help on expression

ganeshreddy — Wed, 08 Nov 2017 07:11:51 GMT

Hi Jyotish,

I realized now, Yes,you are correct, In the data there are no entries which satisfy the given condition.

I am not sure how to calculate, please help me to calculate opportunity value. basically i.e

Exclude the 'MarkrtName' value matched rows to 'POLICYMARKET' strings to get total GWP_GBP opportunity value.

PF my recent reply to Sunny for given example.

Kind Regards,

Ganesh

Re: Help on expression

sunny_talwar — Wed, 08 Nov 2017 09:39:02 GMT

I am afraid, I am not understanding this in words, can you tell me the numbers you would see with the expression like this?

Sum( {<POLICYMARKET<>MarketName)>} [GrossWrittenPremium_GBP])

Re: Help on expression

jyothish8807 — Wed, 15 Nov 2017 08:03:30 GMT

Hi Ganesh,

I think the solution suggested by me should work for you.

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

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

You can check this by manupulating your excel file.

Br,