topic Re: region wise in QlikView

region wise

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

Hi all,

Ruben Marin

i want to show in pivot table top5 customers by each region

i have 3 regions --- so in pivot table i want to show total 15 customers each region 5 customers.

how can i do this?

Thanks

Sony

Re: region wise

pooja_prabhu_n — Tue, 18 Sep 2018 05:47:49 GMT

Hi,

try this

=If(Rank(Sum(Sales), 1, 1) <= 5, Sum(Sales))

Hope this helps

Thanks,

Pooja

Re: region wise

tresB — Tue, 18 Sep 2018 05:47:51 GMT

Try expression like:

If(Rank(Sum(Sales))<6, Sum(Sales))

Re: region wise

soniasweety — Tue, 18 Sep 2018 05:51:18 GMT

thanks but i have to measures

i cant put sum(sales)

dimensions:

customer

satge

Measure

sum({<type={'service'}>amount)

sum({<type={'Harware'}>amount)

the above am using how can i do this?

Re: region wise

rubenmarin — Tue, 18 Sep 2018 06:46:03 GMT

Hi Sony, both answers (Pooja and Tresesco) seems to work. Just use your measure instead of sum(sales). That doesn't works?

Re: region wise

soniasweety — Tue, 18 Sep 2018 07:10:54 GMT

not working

it is showing for each region 15 customers

Re: region wise

rubenmarin — Tue, 18 Sep 2018 07:30:06 GMT

I tried to reproduce it on Pooja's sample but only 5 customers per region are shown. Can you upload a sample or change the attached to show 15 customers by each region?

If(Rank(Sum(Sales), 4, 1) -> '4' to keep only one when there are different customers in the same rank.

Re: region wise

jyothish8807 — Tue, 18 Sep 2018 07:30:50 GMT

Hi Sony,

If you dont want to modify the expression, then try this as dimension.

Replace customer with this expression:

=Aggr(If(Aggr(Rank(Sum(Sales),1,1),Region,Customer)<= 5,Customer),Region,Customer)

Exp: sum(Sales)

Br,

Re: region wise

pooja_prabhu_n — Tue, 18 Sep 2018 07:33:08 GMT

Yeah. It won't work when you have two expressions, If the top 5 customers are different for both the type, then it will display top5 for type=service and top 5 customers for type=Harware.

If you just want top 5 for each region you have to use expression like this:

=If(Rank(sum( {<Type={'service','Harware'}>}amount)) <= 5, sum( {<Type={'service','Harware'}>}amount))

Thanks,

Pooja

Re: region wise

jyothish8807 — Tue, 18 Sep 2018 07:36:34 GMT

Also click on suppress null values.

But it is always recommended to modify the expressions rather using calculated dimensions. So the above mentioned solutions are more optimized

Br,

Re: region wise

soniasweety — Tue, 18 Sep 2018 10:59:33 GMT

its not working in my case chk the attachment

tresesco

pooja.prabhu.n

jyothish8807

Ruben Marin

Re: region wise

rubenmarin — Tue, 18 Sep 2018 11:04:10 GMT

Hi sony, as Pooja saids below, if you have different expressions, those differenct expression are the ones showing other customers, try adding the same condition to all expresions. PFA.

You can check supress null values in dimension to remove the null value.

Re: region wise

soniasweety — Tue, 18 Sep 2018 11:11:29 GMT

sorry ...

Ruben Marin

if i add customername in the dimension it is giving me wronfg result..

check the attached.

actual requirment region should not be in table

dim: accountname

salestage

Re: region wise

jyothish8807 — Tue, 18 Sep 2018 11:25:36 GMT

Hi Sony,

Try this:

=If(Aggr(Rank(Sum(Value)),Region,Account_Name)<7,Sum(Value))

Also refer the attached.

Br,

Re: region wise

soniasweety — Tue, 18 Sep 2018 11:29:04 GMT

but... i want to see the service amount and hardware amount as a measures also

Re: region wise

jyothish8807 — Tue, 18 Sep 2018 11:34:43 GMT

Just copy the same expression and replace you expression for service and hardware

Refer the attachment.

Re: region wise

rubenmarin — Tue, 18 Sep 2018 13:28:50 GMT

Hi Sony, Jyothish answer above is working, isn't?