topic Re: Expression set analysis and renaming values in QlikView

Expression set analysis and renaming values

paul_ripley — Fri, 03 Aug 2018 11:41:39 GMT

Hello

The set analysis below, will bring back results for any portfolio where the GBP proce eds is > 500,000 (excluding portfolio 22701282).

I would like to amend it slightly, so that the below is still correct but with the additional criteria that any portfolio with proceeds < 500,000 is also included but they are grouped together and renamed as 'Other'

Sum(Aggr(

If(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1, 1E6))

, bussline_reclass, portfolio, portfolio_text))/1000000

For example below currently below I would only return Prisa and SLR. I would like to group the rest to be called 'Other' as they are less than 500,000

Could anyone help with this?

Many thanks

Paul

Re: Expression set analysis and renaming values

rick_vansoest — Fri, 03 Aug 2018 11:59:24 GMT

Just set Dimension Limits?

Or you could create a calculated dimension? Something like:

IF( AGGR(

//Your measure

, [Dimension Field]) <= 500,000 , [Dimension Field] ,'OTHERS')

Re: Expression set analysis and renaming values

sunny_talwar — Fri, 03 Aug 2018 12:14:31 GMT

I think this would need you to use calculated dimension instead of set analysis here... would you be able to share a sample to help you better here

Re: Expression set analysis and renaming values

paul_ripley — Fri, 03 Aug 2018 12:21:22 GMT

Hi Sunny

How is the best way to share this?

Thanks

Paul

Re: Expression set analysis and renaming values

sunny_talwar — Fri, 03 Aug 2018 12:23:15 GMT

See if this helps

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Expression set analysis and renaming values

paul_ripley — Fri, 03 Aug 2018 12:39:46 GMT

Hi Sunny

Hopefully you can use the attached

Thanks

Paul

Re: Expression set analysis and renaming values

sunny_talwar — Fri, 03 Aug 2018 12:41:19 GMT

You were not able to reduce it down any further ...

Re: Expression set analysis and renaming values

sunny_talwar — Fri, 03 Aug 2018 12:47:18 GMT

Which chart am I looking at? This one?

Re: Expression set analysis and renaming values

paul_ripley — Fri, 03 Aug 2018 13:16:17 GMT

Yes that is the one, thanks. I Just did the reduce data thing

Re: Expression set analysis and renaming values

paul_ripley — Fri, 03 Aug 2018 13:28:30 GMT

sorry for delay in replying. I don't seem to get emails when I get a reply

Re: Expression set analysis and renaming values

sunny_talwar — Fri, 03 Aug 2018 13:35:50 GMT

Checking

Re: Expression set analysis and renaming values

paul_ripley — Mon, 06 Aug 2018 08:57:53 GMT

Hi Rick

Unfortunately dimension limits didnt work because I had 2 expressions in the table and the portfolio could be zero in 1 expression but > 500,000 in the other

I tried using your code in the expression. The current expression is

=(sum({<entity -= {"0148"},pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282} > } [GPR GBP])*-1)

So I entered the below but it seemed to have an issue with 'OTHERS'. Can you see what ive done wrong?

=IF( AGGR(

(sum(
{
<entity -= {"0148"},
pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282}
>
}
[GPR GBP])*-1)

, portfolio) <= 500,000 , portfolio ,'OTHERS')

Re: Expression set analysis and renaming values

paul_ripley — Mon, 06 Aug 2018 08:58:50 GMT

Hi Sunny

Did you get chance to take a further look?

Thanks

Paul

Re: Expression set analysis and renaming values

rick_vansoest — Mon, 06 Aug 2018 12:28:04 GMT

It had some trouble with the , in the 500,000

Replaced the old dimensions with the new ones:

This calculation is working for me:

=IF( AGGR(

(sum({<entity -= {"0148"},pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282} > } [GPR GBP])*-1)

, portfolio) >= 500000 , portfolio, 'Others')

you could do the same for portfolio_text and Money Multiple (GBP) Depending on the requirements.

Final result:

Re: Expression set analysis and renaming values

rick_vansoest — Mon, 06 Aug 2018 12:41:57 GMT

Dimensions limits gave me the same result. Just make sure you set the expression which you want to use first.

Re: Expression set analysis and renaming values

paul_ripley — Mon, 06 Aug 2018 16:06:34 GMT

Hi Rick

Thanks very much for your help. It is now working

Kind regards

Paul