Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Aggr function within Valueloop

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?

1 Solution

Accepted Solutions
MVP

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

15 Replies
MVP

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

MVP
Partner - Contributor II
Author

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.

Partner - Contributor II
Author

Thanks - very useful documentation!

MVP

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

Partner - Contributor II
Author

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

MVP

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

Partner - Contributor II
Author

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

MVP
Community Browser