topic Re: Value Aggregation in Pivot Table in QlikView

Value Aggregation in Pivot Table

HermGerm — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

im struggeling with a formula in Pivot table. I want to show how many persons worked with a specific machine.My Table basically looks kinda like this

machine day shift Persons

123 18.11.2019 F 5

18.11.2019 S 3

19.11.2019 F 4

19.11.2019 S 4

My Formula in Persons looks like this: =count(DISTINCT person_nr1) which works just fine on the lowest level (the shift).

But if I close all levels and just look in the machine, qlikview obvisouly also applies this formula on the top level (machine) and now shows me distinct persons which worked on the machine (and does not take the other levels like day and shift into account).

But I want the following: Basically Qlik should only apply my formula on the lowest level. On all other levels, it should just Aggregate the Values from the lowest level (down to top). So now Qlik shows a Result like this on the top level

maschine Persons

123 5

I expect this:

123 16

Which is the Aggregation of the distinct persons in every shift.

I hope you understand what i mean. How to do this?

Re: Value Aggregation in Pivot Table

HermGerm — Wed, 20 Nov 2019 07:15:31 GMT

sum(Aggr(count(DISTINCT person_nr1),machine,day,shift))

Could this be a solution? Im not sure, since I do not understand what this formula is doing... Just copied it^^

Re: Value Aggregation in Pivot Table

Brett_Bleess — Wed, 04 Dec 2019 18:24:19 GMT

Andre, have a look at the following Design Blog post, hopefully it may help explain things a bit further, but what you have as a result of your other post makes sense to me, but I am not a guru on this aspect of the product in the least. If that did work for you, you can go ahead and and use the Accept as Solution button on it to mark that as working for you.

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

There are some other posts in this area as well related to AGGR, some additional links are on the post above, but here is the base link to the site in case you want to do some further searching on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

One more I forgot that might be of some help too:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett