Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
);
"")
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))
),
'')
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.
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
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?
Honestly, it is not something easy to explain) But it'll try:
"The lower end is determined as follows:
-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;
The upper end is determined as follows:
-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;
"
Hope this helps)
Regards,
Denis
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))
),
'')