Excel formula to Qlik Sense

[Anonymous]

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.

Could you please help me to find the right wording.

Thanks in advance.

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)

);

"")

[petter]

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.

[Anonymous]

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?

Thanks in advance,

Denis

[petter]

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?

[Anonymous]

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

[Anonymous · Accepted solution]

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))

           ),

'')