recover a result from a dynamic selection

[#1]

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]

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)

[#3]

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]

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