Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
marcus_steggall
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)
1 Solution

Accepted Solutions
jaibau1993
Contributor II

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
jaibau1993
Contributor II

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.

marcus_steggall
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