topic Qliksense use valuelist, aggr() and count() in App Development

Qliksense use valuelist, aggr() and count()

andre_ficken — Fri, 20 Nov 2015 14:25:59 GMT

Hi there, here is my second challenge of the day.... being a relative freshman in Sense.

I need a barchart of customers with a total turnover over 50k, 20-50k and 0-20k. The bar should show total turnover in that categorie and I would also like to show the number of customers that build up the total revenue.

The categories are made with a valuelist, but how can I get the aggregate for each categorie and the count of customers in it??

Thanks for the help, again....

Re: Qliksense use valuelist, aggr() and count()

swuehl — Fri, 20 Nov 2015 15:11:17 GMT

Try a calculated dimension

=Aggr(

if(Sum(TurnOver) <= 20000, Dual('0-20k',1), if(Sum(TurnOver) <=50000,Dual('20-50k',2), Dual('50+k',3))

, Customer)

Then as expression

=Count(Distinct Customer)

resp.

=Sum(TurnOver)

Re: Qliksense use valuelist, aggr() and count()

andre_ficken — Fri, 20 Nov 2015 15:31:11 GMT

my chart dimension is : valuelist('0-20k','20-50k','50+k')

what do I put in the expression of the turnover calculation??

MY current turnover calculation is:

//Sum( {$< [.Product] -= {'Reiskosten'},

// [Bedrijfsnaam] -= {'*Impulsus*','*SCOPE*','*CiEP*'},

// [Productsoort] -= {'Materialen voor de klant','*Intern*','*S&F*'}

// >} [Bedrag])

Where [Bedrag] is the turnover. The otherlines are registered amounts that do not count as turnover.

Following your previous message gives me a expression error on the aggr() function.

Re: Qliksense use valuelist, aggr() and count()

swuehl — Fri, 20 Nov 2015 15:51:06 GMT

I missed a closing bracket in the second line, please try again:

=Aggr(

if(Sum(TurnOver) <= 20000, Dual('0-20k',1), if(Sum(TurnOver) <=50000,Dual('20-50k',2), Dual('50+k',3)))

, Customer)

Re: Qliksense use valuelist, aggr() and count()

andre_ficken — Fri, 20 Nov 2015 16:06:20 GMT

I think the turnover categories have worked as a treat!! Great sofar and thanks for the help...

1 wish remains still: Show the number of customers in the graph with each of the 3 categories.

Do you know if this is possible too??

Re: Qliksense use valuelist, aggr() and count()

swuehl — Fri, 20 Nov 2015 16:14:46 GMT

=Count(Distinct Customer)

as expression

Re: Qliksense use valuelist, aggr() and count()

andre_ficken — Fri, 20 Nov 2015 16:23:50 GMT

the count(distinct) wil then replace the sum(turnover) ? But that is not what I meant... I would like to plot the turnover AND at the same time I would like to show just the count(distinct customers) that build that particular turnover total.

Re: Qliksense use valuelist, aggr() and count()

swuehl — Fri, 20 Nov 2015 16:52:53 GMT

What about creating two expressions?