topic Re: Chart expression - need help in QlikView

Chart expression - need help

linoyel — Mon, 21 Dec 2015 15:16:42 GMT

Hi, I need help with writing a chart expression.

If I have the following data where "Sum of Sales" is an expression: sum([MONTHLY BRUTO SALES])+sum([BRUTO SALES])

Material	Customer	Sum of Sales	Count distinct customer
		184.67	5
abc	1	168.87	1
abc	2	15.00	1
abc	3	0.54	1
abc	4	0.27	1
abc	5	0.00	1

I need to present Number of distinct customers who BOUGHT the selected material - which means that Sum of Sales is supposed to be > 0. I need to get this:

Material	Count distinct customer
abc	4

Thanks!

Re: Chart expression - need help

swuehl — Mon, 21 Dec 2015 15:21:20 GMT

Maybe like

=Sum( Aggr( If( sum([MONTHLY BRUTO SALES])+sum([BRUTO SALES]) >0,1), Material, Customer))

Re: Chart expression - need help

lironbaram — Mon, 21 Dec 2015 15:21:22 GMT

hi you can use

sum(aggr(if(sum([MONTHLY BRUTO SALES])+sum([BRUTO SALES])>0,1,0),Customer,Material))

Re: Chart expression - need help

Anonymous — Mon, 21 Dec 2015 15:24:22 GMT

if (sum([MONTHLY BRUTO SALES])+sum([BRUTO SALES])>0, Count(Material)) should do it

Re: Chart expression - need help

alexandros17 — Mon, 21 Dec 2015 15:29:54 GMT

use this expression:

Count({$ <[Sum of Sales]={'>0'}>} TOTAL DISTINCT Customer)

Re: Chart expression - need help

cleitoncabral2 — Mon, 21 Dec 2015 15:39:02 GMT

Se for mostrar somente o número de clientes pode fazer o seguinte:

count (distinct cliente)

Re: Chart expression - need help

linoyel — Tue, 22 Dec 2015 09:01:44 GMT

Thank you for all your replies.

The formula that worked:

=Sum( Aggr (if($(vSalesSum) > 0, Count(DISTINCT [Customer])), [Material], [Customer]))