
Re: Excel formula to Qlik Sense
Petter Skjolden Aug 21, 2018 7:39 PM (in response to Denis Mefodev)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 Excelfunctions 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.

Re: Excel formula to Qlik Sense
Denis Mefodev Aug 22, 2018 2:57 AM (in response to Petter Skjolden )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

Re: Excel formula to Qlik Sense
Petter Skjolden Aug 22, 2018 5:39 AM (in response to Denis Mefodev)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?

Re: Excel formula to Qlik Sense
Denis Mefodev Aug 22, 2018 6:06 AM (in response to Petter Skjolden )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




Re: Excel formula to Qlik Sense
Denis Mefodev Sep 12, 2018 11:13 AM (in response to Denis Mefodev)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))
),
'')