topic Re: Difference between Aggregate Functions in Dimension and Formel in QlikView

Difference between Aggregate Functions in Dimension and Formel

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

Hi every one,

i have a question as just a beginner, please take a look at my qvw.

There is a difference if i put aggr Function in Dimension or in Fomel.

count( DISTINCT{$ < BelegID={"=aggr(COUNT([BelegID]), %KundeID) = 1 "}, _Launch = {1} >} [%KundeID])

i want to get count of customers with 1,2, 3 and more orders after launch.

Which one is correct? Any why?

Thanks for your responses!

Re: Difference between Aggregate Functions in Dimension and Formel

swuehl — Fri, 03 Jun 2016 09:46:43 GMT

You have %KundeID that are linked to more than 1 FirmaRef

%KundeID	Count(DISTINCT FirmaRef)
	3
613567	2
613741	2
614075	2
614130	2

So you need to create a composite key made of FirmaRef and %KundeID if you want to count the BelegeID correctly per company in a set modifer advanced search.

Have a look at attached modified sample.

Re: Difference between Aggregate Functions in Dimension and Formel

arsa6978 — Fri, 03 Jun 2016 14:01:09 GMT

Hi swuehl,

it works, thanks!

I need now all Orders with SumCent > 0. How could i do this in Set Analysis?

Please have a look at attached modified sample.

Re: Difference between Aggregate Functions in Dimension and Formel

swuehl — Fri, 03 Jun 2016 23:37:51 GMT

Maybe like

Count({<BelegID = {"=Sum(SummeCent)>0"}>} DISTINCT BelegID)

Re: Difference between Aggregate Functions in Dimension and Formel

arsa6978 — Mon, 06 Jun 2016 14:06:34 GMT

Hello again,

for me it is more interesting to know, how could i combine SumCent > 0 with this Expression:

count( DISTINCT{$ < %CompKey={"=count({< _Launch = {1}>}[BelegID]) = 1"}, _Launch = {1} > } [%CompKey])

to concider SumCent > 0 and _Launch = {1}

Thanks!

Re: Difference between Aggregate Functions in Dimension and Formel

swuehl — Mon, 06 Jun 2016 15:08:17 GMT

What would be the internal grouping entity for your SumCent >0 comparison?

I mean, if you would need to create a straight table where something like

=If(Sum(SumCent)>0,1,0)

would be the expression, what would be the correct dimension?

Use this field dimension in your set expression with an advanced search

{< FIELD = {"=Sum(SumCent)>0"} >}

Re: Difference between Aggregate Functions in Dimension and Formel

arsa6978 — Mon, 06 Jun 2016 15:30:59 GMT

Do you mean with advanced search something like this?

count( DISTINCT{$ < BelegID = {"=Sum(SumCent)>0"}, %CompKey={"=count({< _Launch = {1}>}[BelegID]) = 1"}, _Launch = {1} > } [%CompKey])

pls have a look at customers2 table attached.

Re: Difference between Aggregate Functions in Dimension and Formel

swuehl — Mon, 06 Jun 2016 15:40:28 GMT

Maybe, or if you need to filter the BelegID within the CompKey filtering:

count( DISTINCT{$ < %CompKey={"=count({< _Launch = {1}, BelegID = {'=Sum(SummeCent)>0'} >}[BelegID]) = 1"}, _Launch = {1} > } [%CompKey])

Re: Difference between Aggregate Functions in Dimension and Formel

arsa6978 — Mon, 06 Jun 2016 15:47:14 GMT

Exactly, thank you very much!