# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor II

## Excel formula to Qlik Sense

Hi all!

I need to rewrite the following 2 formulas from excel to Qlik Sense. I tried various times (generally by replacing excel formulas with corresponding Qlik ones), but I suppose I made mistakes in syntax.

Upper end formula:

=IFERROR(

IF(MOD(COUNT(D14:F505);4)=0;

AVERAGE(

LARGE(D14:F505;CEILING(COUNT(D14:F505)/4;1));

LARGE(D14:F505;CEILING(COUNT(D14:F505)/4;1)+1));

LARGE(D14:F505;CEILING(COUNT(D14:F505)/4;1))

);

"")

Lower end formula:

=IFERROR(

IF(MOD(COUNT(D14:F505);4)=0;

AVERAGE(

LARGE(D14:F505;COUNT(D14:F505)-CEILING(COUNT(D14:F505)/4;1)+1);

LARGE(D14:F505;COUNT(D14:F505)-CEILING(COUNT(D14:F505)/4;1)));

LARGE(D14:F505;COUNT(D14:F505)-CEILING(COUNT(D14:F505)/4;1)+1)

);

"")

Tags (4)
1 Solution

Accepted Solutions
New Contributor II

## Re: Excel formula to Qlik Sense

if anyone interested, I just simply amended only one function and the formula works:

=alt(if(

mod([FCMU, %],4)=0,

rangeavg(

max([FCMU, %],ceil(([FCMU, %])/4,1)),

max([FCMU, %],ceil(([FCMU, %])/4,1)+1)),

max([FCMU, %],ceil(([FCMU, %])/4,1))

),

'')

5 Replies MVP

## Re: Excel formula to Qlik Sense

The Max() function of Qlik corresponds very closely to the LARGE()-function in Excel. It can take a ranking as the second parameter to retrieve the Nth largest/max value.

The other Excel-functions have more obvious candidates MOD is Mod, CEILING is Ceil, AVERAGE is Avg, COUNT is Count. If at all necessary to use in a Qlik expression maybe the Alt function would be a replacement for the ISERROR.

New Contributor II

## Re: Excel formula to Qlik Sense

Thanks Petter,

I tried to replace all the formulas accordingly.

The below formula is a result of respective replacement:

=alt(if(

mod([FCMU, %],4)=0,

avg(

max([FCMU, %],ceil(([FCMU, %])/4,1)),

max([FCMU, %],ceil(([FCMU, %])/4,1)+1)),

max([FCMU, %],ceil(([FCMU, %])/4,1))

),

'')

Logicall, it seems to be ok, however, "Nested aggregation is not allowed" error appears. Do you have any ideas, how it could be managable?

Denis MVP

## Re: Excel formula to Qlik Sense

It is a fact that directly nested aggregations are not allowed in Qlik. However there is a aggregation helper function called Aggr that can be used to solve this. Sometimes you can also use a search in a set expression which can use aggregations too ...

Can you explain in plain english what this calculation is and how it works?

New Contributor II

## Re: Excel formula to Qlik Sense

Honestly, it is not something easy to explain) But it'll try:

"The lower end is determined as follows:

• if the total number of profit level indicator values divided by 4 is an integer, then the lower end of an arm’s length range is calculated as the arithmetic mean of:

-the profit level indicator value assigned with a serial number in the set which equals this integer and

-the profit level indicator value assigned with the next ascending serial number;

• if the total number of profit level indicator values divided by 4 is a fraction (not an integer), then the lower end of an arm’s length range equals the profit level indicator value assigned with a serial number in the set which matches the integer value of the fraction increased by one.

The upper end is determined as follows:

• if the total number of profit level indicator values multiplied by 0.75 is an integer, then the upper end of an arm’s length range is calculated as the arithmetic mean of:

-the profit level indicator value assigned with a serial number in the set which equals this integer and

-the profit level indicator value assigned with the next ascending serial number;

• if the total number of profit level indicator values multiplied by 0.75 is a fraction (not an integer), then the upper end of an arm’s length range equals the profit level indicator value assigned with serial number in the set which matches the integer value of the fraction increased by one.

"

Hope this helps)

Regards,

Denis

New Contributor II

## Re: Excel formula to Qlik Sense

if anyone interested, I just simply amended only one function and the formula works:

=alt(if(

mod([FCMU, %],4)=0,

rangeavg(

max([FCMU, %],ceil(([FCMU, %])/4,1)),

max([FCMU, %],ceil(([FCMU, %])/4,1)+1)),

max([FCMU, %],ceil(([FCMU, %])/4,1))

),

'')