topic Re: Expression mistake in QlikView

Expression mistake

Tue, 05 Apr 2016 18:24:46 GMT

Hello

I was wondering if anyone can help me,

I got the following expression working fine. I want a formula that adds the values that exceed X. The thing is, when X is a number, it'll work fine, but i need to get a formula in that number, that would be the average of theTotal Sale witht he number of SalesPerson with a Sale.

I currently have that expression in another column, but it wont work using the Column(1) function. I've also tryed to copy the formula for the average (insted of the X) but it wont work either.

sum(Aggr(if(Sum(Aggr((Price_TE), Created_New,SalesPersonName,OrderItem_ID))>=X,

Sum(Aggr((Price_TE), Created_New,SalesPersonName,OrderItem_ID)),

0),Created_New, WeekC,SalesPersonName))

Does anyone got an idea how i can do this?

thanks for the support

Re: Expression mistake

sunny_talwar — Tue, 05 Apr 2016 18:32:12 GMT

Can you share a sample where we can try it out?

Re: Expression mistake

Tue, 05 Apr 2016 20:27:17 GMT

There it is.

I need the last expression of the last table (the red one) to use insted of the value 100000 i wrote there, to be the value from Column(7), or the average of each SalesPerson.

Re: Expression mistake

swuehl — Tue, 05 Apr 2016 22:18:29 GMT

Well, I think your expressions could be simplified, but that would require some more time to look into your data model.

I would assume that you should be able to create similar without the need for so many aggr() functions.

Try something along these lines:

Sum( Aggr( If( Sum(Aggr(Price_TE, Created_New,SalesPersonName,OrderItem_ID))

sum(TOTAL<WeekC,Created_New> Aggr(Price_TE,OrderItem_ID))

/count(TOTAL<WeekC,Created_New> Distinct SalesPersonName),

Sum(Aggr(Price_TE, Created_New,SalesPersonName,OrderItem_ID)),0), Created_New, WeekC,SalesPersonName)

)

Re: Expression mistake

Tue, 05 Apr 2016 22:55:55 GMT

awsome, this worked gr8, had to make few adjustment for the totals but it worked!!!

Thanks alot