topic Re: Line Chart Top 20 Customer Sales in QlikView

Line Chart Top 20 Customer Sales

Louie — Thu, 08 Oct 2020 05:40:15 GMT

I have a request to show the top 20 Customer Sales, By Month on the x-Axis and Sales by Order Type on the y-Axis on a Line Chart. (Refer to screen shot)

One of the expressions to show "Hybris" Sales is as follows:

=SUM({<[Order Type Code] = {'H*'}-{'HS'} >} Value * fSaleToInclude)

My question is:

How do I limit the Sales value returned by the Expression to only Include the Top 20 Customer Sales for Hybris Orders?

Is this possible?

Re: Line Chart Top 20 Customer Sales

edwin — Thu, 08 Oct 2020 15:08:54 GMT

have you tried using the dimension limit - set to 20, and move Hybris as the first expression

Re: Line Chart Top 20 Customer Sales

edwin — Thu, 08 Oct 2020 15:13:41 GMT

add on, its actually based on which expression is sorted first. the tow 20 will then depend on what is on top of your sort order. if you use a straight table, the top 20 will depend on which expression the user clicks in interactive sort

Re: Line Chart Top 20 Customer Sales

Louie — Fri, 09 Oct 2020 00:20:41 GMT

I need to add more detail so that what I want to achieve is a little clearer.

The Line chart contains 7 Expressions: (I have added another screen shot)

Hybirs = SUM({<[Order Type Code] = {'H*'}-{'HS'} >} Value * fSaleToInclude)

Normal = SUM({<[Order Type Code] = {'NO'} >} Value * fSaleToInclude)

eStore = SUM({<[Order Type Code] = {'S*'} >} Value * fSaleToInclude)

Credit/Returns =fabs(SUM({<[Order Type Code] = {'RS', 'BR', 'CN', 'BP'} >} Value * fSaleToInclude))

CBD = SUM({<[Order Type] = {'*CBD*'} >} Value * fSaleToInclude)

Debitss = SUM({<[Order Type Code] = {'DN'} >} Value * fSaleToInclude)

Pick Ups = SUM({<[Order Type Code] = {'PE', 'PU', 'PS', 'PT'} >} Value * fSaleToInclude)

For each of these expressions , I only would like the top 20 Customer Sales for each Month returned.

E.g For Dimension on the x-axis = 2020-Sept, show the Sales value for each expression (which is working), but I also want to only consider the Top 20 Customer sales for Sept 2020 and so on for each other month.

I cannot use Dimension Limits for this scenario as this would only consider the top 20 for the first expression.

Re: Line Chart Top 20 Customer Sales

Louie — Fri, 09 Oct 2020 00:23:23 GMT

Hi Edwin

I actually have 7 expressions. and all of these need to consider the Top 20 Customer sales only.

I have added a comment which provides a little more detail on what I am trying to achieve

Re: Line Chart Top 20 Customer Sales

edwin — Fri, 09 Oct 2020 03:00:21 GMT

if the requirement is to restrict all expressions with the top 20 of one of the expression, then i would first create a variable determining the top 20 based on a single expression, then use that to limit the customers in all the expressions in the chart.

{< .... Customer={$(vTop20Customers)} ... >}

the variable should use the following format:

'xxxx', 'xxxx', 'xxxx'

use concat( distinct chr(39) & Customer & crh(39), ',')

of course you need to add rank into it as set analysis:

=concat(distinct {<{<[Order Type Code] = {'H*'}-{'HS'} >}, Customer={"=rank( SUM({<[Order Type Code] = {'H*'}-{'HS'} >} Value * fSaleToInclude))<=20"}>} chr(39) & Customer & chr(39) ,',')

Re: Line Chart Top 20 Customer Sales

edwin — Fri, 09 Oct 2020 03:01:50 GMT

=concat(distinct {<[Order Type Code] = {'H*'}-{'HS'} >}, Customer={"=rank( SUM({<[Order Type Code] = {'H*'}-{'HS'} >} Value * fSaleToInclude))<=20"}>} chr(39) & Customer & chr(39) ,',')

Re: Line Chart Top 20 Customer Sales

Louie — Mon, 12 Oct 2020 02:41:54 GMT

Hi Edwin

I have applied your suggestion:

This is the expression I now have, however, it is returning zero values:

=sum({< [Order Group] = {'Hybris'}, [Order Type Code] =-{'HS'}, [Order Type] =, [Order Type Desc] =, [Customer ID] = {$(=concat(Distinct {< rank( SUM({<[Order Group] = {'Hybris'}, [Order Type Code] =- {'HS'}, [Order Type]=, [Order Type Desc]= >} Value * fSaleToInclude))<=20} >} chr(39) & [Customer ID] & chr(39),',') )} >} Value * fSaleToInclude)

Can you advise where I have gone wrong?

Note that the following expression is working but does not consider the top 20.

=sum({< [Order Group] = {'Hybris'}, [Order Type Code] =-{'HS'}, [Order Type] =, [Order Type Desc] =, [Customer ID] = {$(=concat(Distinct {<[Order Group] = {'Hybris'}, [Order Type Code] =-{'HS'}, [Order Type] =, [Order Type Desc] = >} chr(39) & [Customer ID] & chr(39),',') )} >} Value * fSaleToInclude)

Re: Line Chart Top 20 Customer Sales

edwin — Mon, 12 Oct 2020 23:48:32 GMT

try it as a variable that is immediately calculated. you want the determination of the top 20 outside of your chart. if you include the determination of the top 20 inside your chart, the top 20 will be determined dynamically and you wont get teh result you need.

vTop20:

=concat(distinct {<[Order Type Code] = {'H*'}-{'HS'} >}, Customer={"=rank( SUM({<[Order Type Code] = {'H*'}-{'HS'} >} Value * fSaleToInclude))<=20"}>} chr(39) & Customer & chr(39) ,',')

dont forget the = sign so it is immediate.

then in your expression

Customer={$(vTop20)} - it almost becomes a static list.

thats actually how you test it: create a text box with

=vTop20

and you should see the top 20 in a comma separated list each enclosed with quotes

Re: Line Chart Top 20 Customer Sales

Brett_Bleess — Thu, 22 Oct 2020 19:25:17 GMT

Just adding a couple of Design Blog posts that may be helpful too:

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

Regards,
Brett