New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
Contributor

Bottom 5 customer issue

Hi team

Quick query, which my eyes cannot see

basically as per attached file, im trying to rank top 5 and bottom 5 customers, within a bar graph.

As you can see from middle table, the top 5 is working perfectly numbers wise.

However, the bottom 5 is nonsense in numbers and i can't see why?

I've done the minus in front of sum, within the calculation (total -Sum)

Any advice would be greatly appreciated.

Thanks

Labels (1)
• Rank

1 Solution

Accepted Solutions Partner

Re: Bottom 5 customer issue

Hi!

There are two mistakes in your file:

1) In your "bottom" chart, you use a rank function. This rank function ranks by "minus the rank function in your top chart". The logic is correct but you missed a sign change. You used

IF(

rank(total -SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R]))<=5,

(SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R])))

But it should be

IF(

rank(total -SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
+ //It has to be a "+"!!
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R]))<=5,

(SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R])))

2) It is not the correct way to shoy top/bottom customers. The right and easiest way is by configuring the "Dimension Limits" tab in your chart properties.

Please, find the attached file (I think that I explained myself quite bad)

Bests,

Jaime.

2 Replies Partner

Re: Bottom 5 customer issue

Hi!

There are two mistakes in your file:

1) In your "bottom" chart, you use a rank function. This rank function ranks by "minus the rank function in your top chart". The logic is correct but you missed a sign change. You used

IF(

rank(total -SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R]))<=5,

(SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R])))

But it should be

IF(

rank(total -SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
+ //It has to be a "+"!!
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R]))<=5,

(SUM({<Year_YYYY={'\$(vMaxYear)'},Period_P=,Period_Start={"<=\$(vMaxDate)"}>}[AMT R])
-
Sum({\$<Year_YYYY={'\$(vPriorYear)'},Period_P=,Period_Start={"<=\$(vPriorYearDate)"}>}[AMT R])))

2) It is not the correct way to shoy top/bottom customers. The right and easiest way is by configuring the "Dimension Limits" tab in your chart properties.

Please, find the attached file (I think that I explained myself quite bad)

Bests,

Jaime.

Highlighted
Contributor

Re: Bottom 5 customer issue

Awesome many thanks,
I swear trying dimension limits, didn't work for me last time hence the rank function!
Appreciate the time and advice Jaime