Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

andre_ficken
New Contributor II

Qliksense use valuelist, aggr() and count()

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....

7 Replies
MVP
MVP

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

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)

andre_ficken
New Contributor II

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

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.

MVP
MVP

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

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)

andre_ficken
New Contributor II

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

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??

MVP
MVP

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

=Count(Distinct Customer)

as expression

andre_ficken
New Contributor II

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

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.

MVP
MVP

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

What about creating two expressions?

Community Browser