topic Re: Bottom 5 customer issue in QlikView

Bottom 5 customer issue

marcus_steggall — Sat, 16 Nov 2024 04:03:45 GMT

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

Re: Bottom 5 customer issue

jaibau1993 — Mon, 25 Mar 2019 12:01:10 GMT

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.

Re: Bottom 5 customer issue

marcus_steggall — Tue, 26 Mar 2019 07:56:54 GMT

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