Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jonathanalm
New Contributor III

Aggr and set analysis

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

1 Solution

Accepted Solutions
cky
New Contributor III

Re: Aggr and set analysis

If you go to qlik.help.com and click on QV or QS, depending on the product, and the select "February 2018" from the version list, you should be able to search for the keywords at the end of the links provided to pull up the pages since this version is out of release and now a production version.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/dollar-sign-ex...

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/dollar-sign-expansio...

12 Replies
Chanty4u
Esteemed Contributor III

Re: Aggr and set analysis

did u tried

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

jonathanalm
New Contributor III

Re: Aggr and set analysis

Hi,

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

Also I need the sum of all timetable minutes.

Kind regards, Jonathan

Highlighted
cesaraccardi
Valued Contributor

Re: Aggr and set analysis

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
Valued Contributor

Re: Aggr and set analysis

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

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

jonathanalm
New Contributor III

Re: Aggr and set analysis

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

jonathanalm
New Contributor III

Re: Aggr and set analysis

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

jonathanalm
New Contributor III

Re: Aggr and set analysis

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

cky
New Contributor III

Re: Aggr and set analysis

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."