Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Partner
Partner

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
Highlighted

Re: Aggr function within Valueloop

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

View solution in original post

15 Replies
Highlighted

Re: Aggr function within Valueloop

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

Highlighted

Re: Aggr function within Valueloop

Highlighted
Partner
Partner

Re: Aggr function within Valueloop

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.

Highlighted
Partner
Partner

Re: Aggr function within Valueloop

Thanks - very useful documentation!

Highlighted

Re: Aggr function within Valueloop

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

Highlighted
Partner
Partner

Re: Aggr function within Valueloop

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

Highlighted

Re: Aggr function within Valueloop

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

Highlighted
Partner
Partner

Re: Aggr function within Valueloop

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

Highlighted

Re: Aggr function within Valueloop