Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
coengiesberts
Partner - Contributor II
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
sunny_talwar

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

View solution in original post

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

coengiesberts
Partner - Contributor II
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.

coengiesberts
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
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.

sunny_talwar

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

coengiesberts
Partner - Contributor II
Partner - Contributor II
Author

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