determining the avg for a calculated expression

[Anonymous]

I am currently trying to determine what the Avg of the results of a calculation that i within an expression.

Table1, amount

CategoryA, 10

CategoryA, 15

CategoryA, 20

CategoryA, 40

Table2, amount

CategoryB, 100

CategoryB, 160

CategoryB, 180

CategoryB, 200

Table3, codenumber

Customer1, 80

Customer2, 80

Customer3, 80

Customer4, 80

What I am trying to do is get the avg of Table1/Table2 for the customers that have code number 80. My file obviously has hundreds of customers with hundreds of codes so i need to replicate this process.

I have tried

=sum(([Table1])/sum([table2])/Count( DISTINCT ([Table3]))) but it isn't working.

Any ideas what to do here?

[sunny_talwar]

How can you Sum(Table1)? Table1 seems to be a text field. I am not entirely sure what you are looking to do, can you elaborate?

[vinieme12]

I think you need flags to do this

So Your expression will now be

=sum({<TableIs={'Table1'}>}amount) / sum({<TableIs={'Table2'}>}amount) / COUNT({<codenumber = {80}>}DISTINCT Customer)

Category     , amount      ,      TableIs

CategoryA     , 10      ,      Table1

CategoryA     , 15      ,      Table1

CategoryA     , 20      ,      Table1

CategoryA     , 40      ,      Table1

,           ,

Category     , amount      ,      TableIs

CategoryB     , 100      ,      Table2

CategoryB     , 160      ,      Table2

CategoryB     , 180      ,      Table2

CategoryB     , 200      ,      Table2

,           ,

Customer     , codenumber      ,      TableIs

Customer1     , 80      ,      Table3

Customer2     , 80      ,      Table3

Customer3     , 80      ,      Table3

Customer4     , 80      ,      Table3

[Anonymous]

I'm sorry, it should be

sum(Table1 Amount)

[vinieme12]

have you tried creating the flags above?

[PrashantSangle]

Hi,

what is your expected output??

Provide sample data with required output.

Regards,