Aggr function within Valueloop

[coengiesberts]

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)-

(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), 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?

[sunny_talwar]

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)-

(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)-

(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), OmzetKlant_KlantID))

[sunny_talwar]

Also look here:

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

[coengiesberts]

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.

[coengiesberts]

Thanks - very useful documentation!

[sunny_talwar]

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

[coengiesberts]

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

[sunny_talwar]

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

[coengiesberts]

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

[sunny_talwar]

See here if you need help

Preparing examples for Upload - Reduction and Data Scrambling