topic Re: Expression strainge behaviour in QlikView

Expression strainge behaviour

Wed, 01 Apr 2015 08:14:49 GMT

Hi there,

I have this expression max(aggr(count (LastUpdateDate), Year)) which I use to count invoices in a text object and it works fine, but when I try to use it in a straight table the results are wrong. We do not have only 3 customers. Any ideas?

P.S. From the numbers I get I see that the first number (6) is the total for 2015 and not for the customer, the secend is for 2014 e.t.c., but it suppose to apply the expression to dimesion, isn't it? (It seems that it does not.)

Thanks,

Helen

Re: Expression strainge behaviour

morganaaron — Wed, 01 Apr 2015 08:18:39 GMT

Hi Helen,

Your textbox expression is counting the number of invoices for the year that had the most invoices. According to your data, this is 2011, which had 3318. This is only therefore a count for 2011, as this has the 'max' amount of invoices out of any of the years that you are aggregating over.

Is that what you want in the table? A count of invoices for every customer for the year that had the maximum amount of invoices? Or what is It you're after?

Re: Expression strainge behaviour

MK_QSL — Wed, 01 Apr 2015 08:51:55 GMT

If you want the same result as per your text box.. you can use below

max(TOTAL <Year> aggr(count (LastUpdateDate), Year))

if this is not you are looking for, kindly let us know your requirement in little brief.. Thanks

Re: Expression strainge behaviour

Wed, 01 Apr 2015 09:22:57 GMT

Hi Aaaron,

You are right. I forgot to change the expression, the correct expression is with sum not max. But the problem still exists. I am sure that I am missing something but what???

Re: Expression strainge behaviour

rubenmarin — Wed, 01 Apr 2015 09:23:10 GMT

Hi Helen,

max(aggr(count (LastUpdateDate), Year, [CRS Πελάτη])) -->Returns the max for customer in one year

max(aggr(count (LastUpdateDate), [CRS Πελάτη])) --> Returns the max for customer (is equal than count (LastUpdateDate) except for the total row)

Re: Expression strainge behaviour

morganaaron — Wed, 01 Apr 2015 09:27:34 GMT

What is it you actually want? Your LastUpdateDate is a counter, so you can simply use Sum(LastUpdateDate) or Count(LastUpdateDate) to return the number of invoices for each customer. Do you only want the count for each customer in a given year?

Re: Expression strainge behaviour

Wed, 01 Apr 2015 09:57:21 GMT

Ruben,

Thank you for your help, but none of them works properly. I have reattach the file with the sum instead of max to see the actual expression.

Sorry for the confusion.

Re: Expression strainge behaviour

morganaaron — Wed, 01 Apr 2015 10:01:57 GMT

Hi Helen,

I'm confused - what do you actually want to show?!

If you just want the count of invoices per customer, why can't you use Sum(LastUpdateDate) or Count(LastUpdateDate)? Your textbox is doing the exact equivalent but unnecessarily aggregating per year and then summing them all up anyway..!

Re: Expression strainge behaviour

rubenmarin — Wed, 01 Apr 2015 10:05:07 GMT

Hi Helen, almost the same, if I understand it right you can use:

sum(aggr(count (LastUpdateDate), [CRS Πελάτη]))

BTW, as said before, this is the same than a simpler "count(LastUpdateDate)"