topic Re: Excluding values in set expression. in QlikView

Excluding values in set expression.

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

Hi All,

I have table as shown in below,and PFA

Please help me on wring expression in UI to calculate the opportunity value,

Exclude the 'Customer' value matched rows to 'opp string' string to get total GWP_GBP opportunity value.

i.e. Opportunity = sum(Exclude the rows from the calculation where 'opp string' includes 'customer' value. So, if anywhere in the pipe separated listed and a single 'customer' appears, the row will be excluded for total oppotunity)

Thanks,

Ganesh

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 09:27:00 GMT

colin_albert‌

tresesco‌

jontydkpi‌

sureshqv‌

jagan‌

Hi All,

Can any one help me on this.

Thanks,

Ganesh

Re: Excluding values in set expression.

YoussefBelloum — Wed, 08 Nov 2017 09:37:08 GMT

Hi,

can you be more clear ?

Exclude the 'Customer' value matched rows to 'opp string' string ....

sum(Exclude the rows from the calculation where 'opp string' includes 'customer'

maybe i don't understand you, but i don't see Customer value

Re: Excluding values in set expression.

prat1507 — Wed, 08 Nov 2017 09:56:53 GMT

Maybe this

=Sum(Aggr(If(SubStringCount([opp string],customer)=0,Sum(Sales),0),customer,[opp string]))

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 10:01:40 GMT

Hi Youssef,

Thanks for you reply,

Yes your understanding is correct, sum(<Exclude the rows from the calculation where 'opp string' includes 'customer'>Sales)

In UI, I have a straight table with customer as a dimension, the above mentioned has to be an expression.

FYI, In the given table first column has customer values.

For example 'tryu' is present in 3 rows i.e row no 1, 7 and 11 respectively so exclude those rows , for 'tryu ' the opportunity value, the value has to be 425 (i.e. 590-(50+85+30)).

Please help me on this.

Best Regards,

Ganesh

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 10:22:57 GMT

Hi Pratyush,

The provided expression is not working, only getting two rows in output and those values are not correct.

However the expected result for 'kitent' is 360 i.e(590-(30+40+80+60+20))

Please find attached data.

Thanks,

Ganesh

Re: Excluding values in set expression.

YoussefBelloum — Wed, 08 Nov 2017 10:39:04 GMT

Find attached,

you can improve it

hope it helps !

Re: Excluding values in set expression.

YoussefBelloum — Wed, 08 Nov 2017 10:47:42 GMT

you can also add:

=if(GetSelectedCount(customer)<>0,

if(only( {<customer=>} wildmatch(replace([opp string],'|',''),'*'&$(x)&'*'))<>1,

sum({<customer=>}sales),'NOK'), sum(sales))

just to always have the total if no customer is selected.

Re: Excluding values in set expression.

prat1507 — Wed, 08 Nov 2017 10:57:45 GMT

Please find the desired app.

Regards

Pratyush

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 11:20:13 GMT

Hi Youssef,

Thanks for your efforts on this, unfortunately our requirement is no to select any customer name, the default out put has to be like tihs.

How to achieve the required output.

Thanks,

Ganesh

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 11:24:04 GMT

Thank you Pratyush.

Re: Excluding values in set expression.

ganeshreddy — Wed, 08 Nov 2017 12:00:20 GMT

Hi Pratyush,

Though it is a correct answer i need to show sale amount as well in the report,but we are getting totals in sale amount column,this is because of the newly created dimension (customer2) PFB,

The output has to be like this,

Is there any workaround that we can avoid new dimension?

Thanks,

Ganesh