recover a result from a dynamic selection - Qlik Community

Report Inappropriate Content · ‎2012-02-13

Hello,

I've met a problem with a application and i need some help.

context :

I have two tables :

Customer : it ‘s a table with a lot of information about customer and this table is joined to an other table “ZBA” by the code Customer “Id_ Customer ”

ZBA : is composed of a lot of fields but the principals are : the instructing shall wich is the key and is the link to the “ZBA” ( the instructing shall has the name “Id_ Customer “) and the ‘payer’ (wich is in fact an “ID_CLIENT”)

To resume in my table ZBA, I ve these :

instructing shall =(id_cusomer) ! Payer ! total !

a ! b ! 12 !

a ! c ! 2 !

! c ! 10 !

! b ! 10 !

r ! e ! 10 !

r ! b ! 10 !

r ! d ! 10 !

The problem with the application now and with the link I’ve realised is that if I select a Customer for example the ‘a’, I ve only two rows :

a ! b ! 12 !

a ! c ! 2 !

and I would like to have all the rows associated to the id_cusomer add to all the rows associated to the payers concerned by the id_customer

so to resume I would like to have for the id_customer =’a’ :

a ! b ! 12 !

a ! c ! 2 !

! c ! 10 !

! b ! 10 !

For id_customer =’r’ :

r ! e ! 10 !

r ! b ! 10 !

r ! d ! 10 !

! c ! 10 !

! b ! 10 !

So , I would like to know how could I do to obtain theses rows in straight table when i select one client .

if you have understood and if you have any idea to resolve this problem.

Thank for help.

Anonymous · ‎2012-02-13

It is possible with set analysis. Create a straight table with dimensions id_customer and Payer, and expression

=sum({<id_customer=, Payer=P(Payer)>} total)

Report Inappropriate Content · ‎2012-02-14

Hello, thanks for your answer

it works very well

But i would like to make a graph (histogram) with just only one dimension id-customer to have the best view of my top ten clients wich have the biggest total.

And so , i ve tried to do that :

total=aggr(sum({<id_customer=, Payer=P(Payer)>} total),Payer)

but i doesn't work, i don't find the same result that i had with two dimensions.

I don't have nay idea to do that,

thanks again for your answer and your help.

Anonymous · ‎2012-02-15

Try to add sum() around your expression:

sum(aggr(sum({<id_customer=, Payer=P(Payer)>} total),Payer))

If it doesn't help, your best bet to solve it is to aggregate per Payer in the script, for example:

SumTotal:
load
Payer,
sum(total) as SumTotal
resident data
group by Payer;

After that, your expression in chart will be simply sum(SumTotal).