Aggr and set analysis

[Jonathan_Alm]

Hi,

I have a problem with combining set analysis, aggr and dividing two expressions.

I need this to be able to create my heatmap.

I am following example on this page:

https://qlikfreak.wordpress.com/2014/03/09/heat-map/

The expression I use and that fails is:

=$(=max(aggr(sum(AbsenceTime)/sum(TimeTableMinutes),AbsenceDate)))

The expression returns: Error: Garbage after expression: ","

If I remove "$(=" And just enter:

=max(aggr(sum(AbsenceTime)/sum(TimeTableMinutes),AbsenceDate)))

I get the correct value: 0,094 which is the date with the highest absence percentage based on TimeTableMinutes.

Also If I remove the "sum(TimeTableMinutes)" the expression is accepted:

=$(=max(aggr(sum(AbsenceTime),AbsenceDate)))

However I need it to be "pre-calculated” outside the pivot table and not for each cell, therefore the "$(=" seems necessary

Does anyone have a suggestion on how to get pass this problem?

Kind regards, Jonathan

[Chanty4u]

did u tried

=$(=max(aggr(sum(AbsenceTime)/(TimeTableMinutes),AbsenceDate)))

[Jonathan_Alm]

Hi,

that creates the same error message: Garbage after expression: ","

Also I need the sum of all timetable minutes.

Kind regards, Jonathan

[cesaraccardi]

Hi Jonathan,

It looks like the aggr() function is interpreting the fraction part of 0,094 as the second parameter (dimension) when you use the dollar expansion to evaluate the expression. You could maybe try changing the number format to '0.000' to check if that is the issue.

Kind Regards,

Cesar

[cesaraccardi]

What if you use the total qualifier, does that work for what you need?

=max(aggr(sum(<total> AbsenceTime)/sum(<total> TimeTableMinutes),AbsenceDate)))

[Jonathan_Alm]

Hi, that must be it.

I replaced the dimensions with data instead just to see and I get it to evaluate the expression:

=$(=max(aggr(10/10, AbsenceDate)))

It results in 1

However if I change it to

=$(=max(aggr(1/10, AbsenceDate)))

I get the same error again.

I have also tried to add a replace around the numbers:

=$(=max(aggr(replace(1/10, ',' , '.'),AbsenceDate)))

But that does not help. Same error again.

Kind regards, Jonathan

[Jonathan_Alm]

Hi Cesar,

thanks for your input but the total qualifier gives med the total absenceTime and total timetableMinutes for all posts. It does not calculate outside the pivot. I need the date where the max absence occured.

Kind regards, Jonathan

[Jonathan_Alm]

Info:

I have reported this as a bug and they have forwarded it to R&D.

I will post any updates when I have an answer for this.

//Jonathan

[Anonymous]

Notice that you will need to write the formula in this format:

=$(#=max(aggr(1/10, Dim1)))

The '#' before Dollar-sign expansion is used in this issue to make QlikView interpret it as numeric values. If not, it is evaluated as text and used the comma separator giving a parse error.

Generally, for numeric variable expansions, the following syntax is used:

$(#variablename)

It always yields a valid decimal-point representation of the numeric value of the variable, possibly with exponential notation (for very large/small numbers). If variablename does not exist or does not contain a numeric value, it will be expanded to 0 instead."

[Anonymous]

http://betahelp.qliktech.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/dollar-sign-exp...

http://betahelp.qliktech.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/dollar-sign-e...