topic Re: Aggr function within Valueloop in QlikView

Aggr function within Valueloop

coengiesberts — Tue, 15 May 2018 14:42:48 GMT

Hello,

I have created a Balanced Score Card Dashboard in QV12 using the Valueloop function. This allows me to enter the data cell by cell.

All works well except for one calculation that uses an aggregation:

SUM(AGGR(

IF((sum({1<OmzetKlant_ArtGrID-={9976,9961,9962,9963,9964,9966,9974,9975},OmzetKlant_VertegenwoordigerID={21,22,26,28}, CLJaar = {$(=Max(CLJaar))}, CLPeriode={$(=Max(CLPeriode))} >} OmzetKlant.Bedrag)-

(sum({1<OmzetKlant_ArtGrID-={9976,9961,9962,9963,9964,9966,9974,9975},OmzetKlant_VertegenwoordigerID={21,22,26,28}, CLJaar = {$(=Max(CLJaar)-1)}, CLPeriode={$(=Max(CLPeriode))}>} OmzetKlant.Bedrag)*1.03))

>0,

(sum({1<OmzetKlant_ArtGrID-={9976,9961,9962,9963,9964,9966,9974,9975},OmzetKlant_VertegenwoordigerID={21,22,26,28}, CLJaar = {$(=Max(CLJaar))},CLPeriode={$(=Max(CLPeriode))} >}OmzetKlant.Bedrag)-

,0), OmzetKlant_KlantID))

The function calulates which customers have a growth of over 3% in turnover; the ones that do, their growth is summarized.

This function works great as an expression within a regular table, but it returns 0 in my BSC table.

My workaround is creating a text box and positioning this on top of the correct cell of the BSC Dashboard, but that looks poor as positioning with QV and Accesspoint differ.

Does anyone have suggestions for a formula that I can use in my BSC Dashboard?

Re: Aggr function within Valueloop

sunny_talwar — Tue, 15 May 2018 15:46:28 GMT

May be try this

SUM(AGGR(NODISTINCT

IF((sum({1<OmzetKlant_ArtGrID-={9976,9961,9962,9963,9964,9966,9974,9975},OmzetKlant_VertegenwoordigerID={21,22,26,28}, CLJaar = {$(=Max(CLJaar))}, CLPeriode={$(=Max(CLPeriode))} >} OmzetKlant.Bedrag)-

>0,

(sum({1<OmzetKlant_ArtGrID-={9976,9961,9962,9963,9964,9966,9974,9975},OmzetKlant_VertegenwoordigerID={21,22,26,28}, CLJaar = {$(=Max(CLJaar))},CLPeriode={$(=Max(CLPeriode))} >}OmzetKlant.Bedrag)-

,0), OmzetKlant_KlantID))

Re: Aggr function within Valueloop

sunny_talwar — Tue, 15 May 2018 15:46:59 GMT

Also look here:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

Re: Aggr function within Valueloop

coengiesberts — Thu, 17 May 2018 06:54:27 GMT

Yes, adding NODISTINCT or DISTINCT to the formula do display a result in the table. The results are different though, so I have to investigate more thoroughly.

Re: Aggr function within Valueloop

coengiesberts — Thu, 17 May 2018 06:55:22 GMT

Thanks - very useful documentation!

Re: Aggr function within Valueloop

sunny_talwar — Thu, 17 May 2018 08:50:13 GMT

Having nothing after Aggr( is equivalent of having DISTINCT in there... Did adding DISTINCT also helped show the results? or did just NODISTINCT help? I would think the NODISTINCT will change the results and DISTINCT won't

Re: Aggr function within Valueloop

coengiesberts — Thu, 17 May 2018 09:49:42 GMT

You are correct. Adding DISTINCT did not make a difference. Adding NODISTINCT gave a result in the table but the number was too high.

Re: Aggr function within Valueloop

sunny_talwar — Thu, 17 May 2018 10:58:08 GMT

There might be an alternate way to solve your problem... are you able to share a sample to check this out?

Re: Aggr function within Valueloop

coengiesberts — Thu, 17 May 2018 13:45:47 GMT

Well, let me see what I can do to anonimize the data

Re: Aggr function within Valueloop

sunny_talwar — Thu, 17 May 2018 13:46:21 GMT

See here if you need help

Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggr function within Valueloop

coengiesberts — Thu, 24 May 2018 06:23:59 GMT

Hi Sunny,

I have attached an example. The table shows 3 lines:

- line 1 shows aggr function that shows correct result

- line 2 shows aggr function that shows incorrect result (0)

- line 3 shows aggr function that shows incorrect result based on nodistinct

I have also added a text object that returns the correct result.

Re: Aggr function within Valueloop

sunny_talwar — Thu, 24 May 2018 09:11:41 GMT

I am not sure I have answer for why this doesn't work with ValueLoop, but you can try with an Island table. I add this table your dashboard using binary load

Dim:

LOAD RowNo() as Dim

AutoGenerate 5;

and now use Dim as dimension rather than using ValueLoop

Re: Aggr function within Valueloop

coengiesberts — Thu, 24 May 2018 11:45:57 GMT

Interesting. Looks like an easy approach. I don't understand why the first line of the original table shows a different number now.

I have created the Dim dimension and replaced the valueloop() with Dim in my actual app. I'm not getting it to work though, it still returns a 0. Did I miss anythihng?

Re: Aggr function within Valueloop

sunny_talwar — Thu, 24 May 2018 11:51:14 GMT

Did you add Dim to Aggr()'s dimension?

Re: Aggr function within Valueloop

coengiesberts — Thu, 24 May 2018 11:53:22 GMT

That's the trick!

Re: Aggr function within Valueloop

sunny_talwar — Thu, 24 May 2018 11:55:12 GMT

Yup, because ValueLoop is not a real dimension... it can't be added to Aggr() and that is why Dim works and ValueLoop doesn't