topic Re: Logic Help in opportunity caliculaton? in QlikView

Logic Help in opportunity caliculaton?

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

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

Re: Logic Help in opportunity caliculaton?

tresB — Wed, 15 Nov 2017 05:12:01 GMT

A smaller sample would attract more replies I guess.

Re: Logic Help in opportunity caliculaton?

tresB — Wed, 15 Nov 2017 05:22:16 GMT

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))

Re: Logic Help in opportunity caliculaton?

ganeshreddy — Wed, 15 Nov 2017 07:05:11 GMT

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..

Re: Logic Help in opportunity caliculaton?

tresB — Wed, 15 Nov 2017 07:14:58 GMT

Use the following expression:

if($(vShowColumn) = 0, 0,

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

)

Re: Logic Help in opportunity caliculaton?

ganeshreddy — Wed, 15 Nov 2017 07:27:26 GMT

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

Re: Logic Help in opportunity caliculaton?

tresB — Wed, 15 Nov 2017 07:29:49 GMT

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