Max and percentage calculation help

[mahitham]

Hi Experts,

Can any one please help me to do below two calculations. I am very new to these calculations. Please help me on this.

CALCULATIONS:

1. For each branch ,need to derive FTE based on below calculation

FTE = Max(0,VALUE+Max(AMOUNT)/5)+0.01

2. For each Name and each BRANCH Name need to calculate the below percentage column based on below calculation

TME%=SUM OF FTE IN THE BRANCH/SUM OF ALL FTE.

I have the fields with sample data like below

Name        BRANCH             VALUE            AMOUNT

KELLY      Sales                   1258693           53685

KELLY      Sales                   4869306           96873

KELLY      Products              4869386           96863

KELLY      Products              4869869           92639

JOHN       ISON                    78563.58           86943

GOA        OURMT                 8563216           869321

GOA         Products              4869869           9686353

Thanks in advance.

[petter · Accepted solution]

This might be in the direction you want:

The FTE is a measure with the following expression and label as FTE:

    Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01

The TME% is a measure witth this expression and label as TME%:

    FTE / RangeSum(Above(FTE,0,RowNo(TOTAL)))

In the above expression FTE refers directly to the FTE column calculated values but it could also be written:

   Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 / Above( Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 , 0 , RowNo(TOTAL)))

obviously the expression with references to the FTE column is to prefer....

[petter]

It should be very similar like this:

FNE:

Sum(VALUE)-Max(TOTAL AMOUNT)/5-0.01

TNE%:

FNE / RangeSum(Above(FNE,0,RowNo(TOTAL)))

I have assumed that the outer aggregation of Max in the FTE and the outer aggregation of Min in the FNE is just functions to pick the lowest for two values: 0 or the calculation and might not be necessary the way Qlik handles the calculations. I might be wrong since I don't know the full nature of your data with such a small sample.

So for this reason it might be necessary to wrap both the FTE Max and FNE Min in RangeMax and RangeMin functions that would be similar to the Excel Min and Max functions.

FTE would then be:

RangeMax( 0 , Sum(VALUE)+Max(TOTAL AMOUNT)/5+0.01 )

FNE would then be:

RangeMin( 0 , Sum(VALUE)-Max(TOTAL AMOUNT)/5-0.01 )