Aggreagtion in qliksense

[Aspiring_Developer]

Hello All,

I have below visual with drill down dimension:-

Expression :-

1) %input comppletion

if(GetSelectedCount([User.Divison Name])>0,(sum(aggr((Sum({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} NB_HOURS)

/

(max({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>}Real_ActualDays)*8)),[Person Name])) /count([Person Name])),

if(
GetSelectedCount([User.Department Name])>0 ,(Sum({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} NB_HOURS)

/

(max({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>}Real_ActualDays)*8)),

(SUM(AGGR(sum(aggr(Sum({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} NB_HOURS)

/

(max({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>}Real_ActualDays)*8),[Person Name])) /count([Person Name]),[User.Department Name])))/COUNT( distinct[User.Department Name])

)

===============

-->The value I see for Clean Energy System is 6.6% (0.9 + 7.5+24.4=32.8 , now 32.8/5 => 6.56 -- > 6.6 % ). 

-->When i click on it CES, it expands and the sum of all departments coming under this division is calculated  i.e 32.8 and further divided with count of records i.e 5.

The calculation is working fine for first row, but when i check it for second row record z, it gives wrong value:-

The correct value should be :-(0.0+0.0+23.8= 23.8, further 23.8/3=>7.9% but in the above table my value is coming 0.0%

When we click on group, it should show the sum of values coming under the Group division divided by the number of records.

Can anyone please help me ? I am not able to understand why it is working fine for first record and not for the remaining ?

@sunny_talwar @swuehl 

Thanks in advance

[swuehl]

Hi, I assume that you experience some sort of the Aggr() function grain mismatch described here:

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

As far as I see, you are using nested Aggr() functions, and at least one Aggr() function seems to use [User.Department Name] as only dimension. 

[Aspiring_Developer]

Hi @swuehl 

Thanks for your response, I read the article that you shared above.

However, I am still not able to achieve what is  needed. Can you please help me ?

[swuehl]

There is not much information given how your data looks like and what you want to calculate in detail.

Trying to understand what you've presented, you might be able to calculate your numbers even without the aggr() function, have a look at

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

https://community.qlik.com/t5/Qlik-Design-Blog/Additive-and-Non-Additive-Numbers/ba-p/1469714