topic Aggreagtion in qliksense in App Development

Aggreagtion in qliksense

Aspiring_Developer — Tue, 05 Oct 2021 15:00:19 GMT

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

Re: Aggreagtion in qliksense

swuehl — Tue, 05 Oct 2021 15:46:02 GMT

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.

Re: Aggreagtion in qliksense

Aspiring_Developer — Tue, 05 Oct 2021 16:28:35 GMT

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 ?

Re: Aggreagtion in qliksense

swuehl — Thu, 07 Oct 2021 10:49:51 GMT

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