topic Re: max sum in QlikView

max sum

seahyanzheng — Wed, 20 Dec 2017 12:39:00 GMT

I have a dimension Step and expression - Unique Customer (sum([Customer Count])). i want to create max of Unique Customer but unable to do so with max(sum([Customer Count])). How do i get the results as shown below?

Re: max sum

el_aprendiz111 — Wed, 20 Dec 2017 17:17:13 GMT

Hi,

1 exmpl

max(total aggr(Count(Customer),Customer ))

Re: max sum

sunny_talwar — Wed, 20 Dec 2017 17:19:26 GMT

I think it would be like this

Max(TOTAL Aggr(Sum([Customer Count]), Step))

Re: max sum

seahyanzheng — Thu, 21 Dec 2017 01:15:14 GMT

I got the total of all customer count instead of the maximum though..

Re: max sum

seahyanzheng — Thu, 21 Dec 2017 01:33:59 GMT

I will like to get max([Unique Customer]). but QV is throwing error - bad field name(s) for Unique customer as it is a label

Re: max sum

sunny_talwar — Thu, 21 Dec 2017 16:45:12 GMT

Would you be able to post screenshots without label names and extended columns so that I can see what expressions you are using? Also, Step is the only dimension in this chart? or are there more then that?

Re: max sum

seahyanzheng — Fri, 22 Dec 2017 02:27:14 GMT

Hi Sunny,

Sure. Below is the screenshot excluding label. Also, Step is the only dimension in this chart and i did suppress when it is null value. Third column will be Max(TOTAL Aggr(Sum([Customer Count]), Step_Nnull))

Thanks for your help.

Re: max sum

sunny_talwar — Fri, 22 Dec 2017 16:34:22 GMT

Can you add another expression and share a screenshot (Max without total)

Max(Aggr(Sum([Customer Count]), Step_Nnull))

Re: max sum

seahyanzheng — Sat, 23 Dec 2017 07:09:37 GMT

Sure.

This is how it looks like:

Re: max sum

pradosh_thakur — Sat, 23 Dec 2017 08:14:49 GMT

i think it is showing the correct data. is there any null value in step_Nnull field. My guess is you have null and the Sum([Customer Count]) for that is 506,014,721

try to uncheck suppress null from dimension and use these expression. share the screenshot after that. null should not be suppressed.

Sum([Customer Count])

Max(TOTAL Aggr(Sum([Customer Count]), Step))

regards

Pradosh

Re: max sum

seahyanzheng — Sat, 23 Dec 2017 09:02:17 GMT

Sure.

This is how it looks like after removing suppression:

Many thanks for all help.

Re: max sum

pradosh_thakur — Sat, 23 Dec 2017 09:16:37 GMT

Exactly what i was suspecting. If you scroll down you can see a null value in dimension and it has the max value which is getting shown . so to ignore the null and to have 612091 as max in all rows use if(len(trim(Step_Nnull))>0,Step_Nnull) as dimension.

regards

Pradosh

Re: max sum

pradosh_thakur — Sat, 23 Dec 2017 09:31:45 GMT

or with suppress null

Max(TOTAL Aggr(Sum({< Step_Nnull ={"*"}>}[Customer Count]), Step_Nnull))

as expression

Re: max sum

seahyanzheng — Sat, 23 Dec 2017 09:32:25 GMT

Thanks i get it now!

But for some reason the calculated dimension is not working though.

Re: max sum

pradosh_thakur — Sat, 23 Dec 2017 09:38:05 GMT

try the above expression with Step_Nnull as dimension and suppress null.

Re: max sum

seahyanzheng — Sat, 23 Dec 2017 09:39:38 GMT

Wow. Saw your second reply and it works! Thank you very much.

Re: max sum

seahyanzheng — Sat, 23 Dec 2017 09:42:33 GMT

Many thanks to Pradosh and Sunny!