Qlik Community

Qlik Sense App Development

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

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Contributor

Combined Pareto class dimension

I am working on a Pareto dashboard.  My work so far is based on this article:

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

My customer Pareto dimension works fine:

 

= Aggr(
  If(Rangesum(Above(Sum({1} Revenue), 1, RowNo())) /
     Sum({1} TOTAL Revenue) < 0.8, 'A', 'B'),
  (Customer, (=Sum({1} Revenue), Desc))
)

 

cust.png

My product Pareto dimension also works fine:

 

= Aggr(
  If(Rangesum(Above(Sum({1} Revenue), 1, RowNo())) /
     Sum({1} TOTAL Revenue) < 0.8, 'A', 'B'),
  (Product, (=Sum({1} Revenue), Desc))
)

 

prod.png

Now, I am trying to figure out how to make a combined class dimension.  In other words, if a straight table row contains an A customer and an A product, the combined class should be AA.

Naively, I tried this for the dimension (just concatenating the other two dimensions):

 

= Aggr(
  If(Rangesum(Above(Sum({1} Revenue), 1, RowNo())) /
     Sum({1} TOTAL Revenue) < 0.8, 'A', 'B'),
  (Customer, (=Sum({1} Revenue), Desc))
)
& Aggr(
  If(Rangesum(Above(Sum({1} Revenue), 1, RowNo())) /
     Sum({1} TOTAL Revenue) < 0.8, 'A', 'B'),
  (Product, (=Sum({1} Revenue), Desc))
)

 

As you can see in the picture below, it doesn't work right.  I guess it makes sense that it doesn't work since I'm just concatenating rows from two virtual tables of different lengths with no meaningful relationship to one another.  I need to "join" them somehow.

combined.png

Note that I cannot do this work in SQL or in the load script because I will eventually replace the above set analysis ({1}) with something more complex that facilitates limited interactivity (dynamically updating Pareto classes based on date selections).

I attached the .qvf file, and here's just the data:

 

SalesData:
LOAD * inline [
Customer|Product|Revenue
Ford|M8|4
Ford|M8|15
Ford|M5|3
Ford|M10|20
Ford|3/8|10
GM|M5|3
GM|M10|25
GM|M8|30
GM|3/8|10
Toyota|M8|6
Toyota|M10|3
Toyota|M6|1
Honda|M12|5
Honda|M8|1
FCA|M5|1
FCA|M8|1
Chrysler|M5|4
] (delimiter is '|');

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted

Re: Combined Pareto class dimension

May be try adding NODISTINCT within the Aggr() functions

=
Aggr(NODISTINCT
    If(Rangesum(Above(Sum({1} Revenue) / Sum({1} TOTAL Revenue), 1, RowNo())) < 0.8, 'A', 'B'),
    (Customer, (=Sum({1} Revenue), Desc))
) &
Aggr(NODISTINCT
    If(Rangesum(Above(Sum({1} Revenue) / Sum({1} TOTAL Revenue), 1, RowNo())) < 0.8, 'A', 'B'),
    (Product, (=Sum({1} Revenue), Desc))
)

 

image.png

View solution in original post

2 Replies
Highlighted

Re: Combined Pareto class dimension

May be try adding NODISTINCT within the Aggr() functions

=
Aggr(NODISTINCT
    If(Rangesum(Above(Sum({1} Revenue) / Sum({1} TOTAL Revenue), 1, RowNo())) < 0.8, 'A', 'B'),
    (Customer, (=Sum({1} Revenue), Desc))
) &
Aggr(NODISTINCT
    If(Rangesum(Above(Sum({1} Revenue) / Sum({1} TOTAL Revenue), 1, RowNo())) < 0.8, 'A', 'B'),
    (Product, (=Sum({1} Revenue), Desc))
)

 

image.png

View solution in original post

Highlighted
Contributor

Re: Combined Pareto class dimension

Awesome, thanks!


For others who find this solution and wonder what NODISTINCT does, I found this post with an example similar to my situation helpful:
https://community.qlik.com/t5/QlikView-App-Development/What-NODISTINCT-parameter-does-in-AGGR-functi...