topic Re: determining the avg for a calculated expression in QlikView

determining the avg for a calculated expression

Anonymous — Tue, 12 Jul 2016 14:54:54 GMT

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?

Re: determining the avg for a calculated expression

sunny_talwar — Tue, 12 Jul 2016 15:24:29 GMT

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?

Re: determining the avg for a calculated expression

vinieme12 — Tue, 12 Jul 2016 15:51:04 GMT

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

Re: determining the avg for a calculated expression

Anonymous — Tue, 12 Jul 2016 16:39:50 GMT

I'm sorry, it should be

sum(Table1 Amount)

Re: determining the avg for a calculated expression

vinieme12 — Wed, 13 Jul 2016 07:06:07 GMT

have you tried creating the flags above?

Re: determining the avg for a calculated expression

PrashantSangle — Wed, 13 Jul 2016 07:20:13 GMT

Hi,

what is your expected output??

Provide sample data with required output.

Regards,