topic Re: Aggr and set analysis in QlikView

Aggr and set analysis

Jonathan_Alm — Fri, 26 Feb 2016 10:33:03 GMT

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

Re: Aggr and set analysis

Chanty4u — Fri, 26 Feb 2016 11:06:32 GMT

did u tried

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

Re: Aggr and set analysis

Jonathan_Alm — Fri, 26 Feb 2016 11:10:09 GMT

Hi,

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

Also I need the sum of all timetable minutes.

Kind regards, Jonathan

Re: Aggr and set analysis

cesaraccardi — Fri, 26 Feb 2016 11:59:55 GMT

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

Re: Aggr and set analysis

cesaraccardi — Fri, 26 Feb 2016 12:05:12 GMT

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

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

Re: Aggr and set analysis

Jonathan_Alm — Fri, 26 Feb 2016 12:34:07 GMT

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

Re: Aggr and set analysis

Jonathan_Alm — Fri, 26 Feb 2016 12:37:11 GMT

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

Re: Aggr and set analysis

Jonathan_Alm — Thu, 03 Mar 2016 10:44:51 GMT

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

Re: Aggr and set analysis

Anonymous — Fri, 02 Mar 2018 00:47:57 GMT

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

Re: Aggr and set analysis

Anonymous — Fri, 02 Mar 2018 00:49:10 GMT

http://betahelp.qliktech.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/dollar-sign-expansion-using-variable.htm?q=numeric%20expansion

http://betahelp.qliktech.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/dollar-sign-expansion-using-variable.htm

Re: Aggr and set analysis

Jonathan_Alm — Mon, 12 Mar 2018 06:10:47 GMT

Hi,

I cannot follow your links?

Are they correct?

Re: Aggr and set analysis

Anonymous — Mon, 12 Mar 2018 13:59:11 GMT

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-expansion-using-variable.htm

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/dollar-sign-expansion-using-variable.htm

Re: Aggr and set analysis

Jonathan_Alm — Tue, 13 Mar 2018 09:59:10 GMT

Hi,

I will definitely look more deeply into this.

Thanks for the input.

Kind regards, Jonathan