Dynamically add fields to aggr

[dominicmander]

I have an expression that requires the addition of a dimension using aggr e.g.

sum(aggr(<some expression>,[Category]))

I use this expression in a host of different charts, with different dimensions, which means that each time I need to add the relevant dimensions to the aggr for the expression to calculate ... e.g.

Bar chart by Month ... sum(aggr(<some expression>,[Month],[Category]))

Table by Region ... sum(aggr(<some expression>,[Region],[Category]))

Table by Drill-down group ... sum(aggr(<some expression>,<current drill down group field>,[Category]))

Pivot table by Region and Month ... sum(aggr(<some expression>,[Region],[Month],[Category]))

etc

In a perfect world I'd like to be able to add this expression to the master items and allow my users to be able to drop it into a chart with any of these dimensions.

Therefore, I need a way to derive the dimensions in play on an expression at a particular time. GetCurrentField() isn't available to me in Sense and would only cover the drill-down option anyway. Dimensionality() returns the number of the dimensions but not the field names. Even with a parametised dollar expansion I wouldn't be able to create one master item (though this would at least make maintenance of the expression easier). Anyone know of any tricks?

[Oleg_Troyansky]

Yes, you can do that using $-sign expansions. Add a $-sign expression with a formula that returns a comma-separated list of desired dimensions or an empty string if no dimensions need to be added. Assuming that the list of Dimensions to choose from is a Field, the calculation will likely be based on the CONCAT() function.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[dominicmander]

‌Hi Oleg,

I can see how I could make it easier for myself to code in different dimensions in different situations as you describe, but that rather defies the point of having a master item measure that my users can deploy in their own objects.

What I really need is a way for the expression to "detect" the dimensions being used in the chart it is added to so that it can adjust the dimensions in the aggr accordingly.

[Oleg_Troyansky]

Hi Dominic,

My thought was that you offer your users a list of available Dimensions and they can select what dimensions should participate in the chart. That's a technique that is common in QlikView. I realize that it might not be as useful in Qlik Sense.

Unfortunately, there is no function (AFAIK) in either one of the two Qlik products, that could tell you dynamically what dimensions have been selected for a given chart.

Would make a cool Idea, though!

cheers,

Oleg

[paul_scotchford]

Hello Dominic

Another way to make expressions commonly available to users is to provide a template App with master items predefined to variables that will contain the calculation expression and are loaded at App process time from the

dictionary.

The calculation expression is defined in a Business Dictionary that resides e.g. in SQL Server and is maintained by

business stake holders.

Self service user will have available all possible calculations for their domain of interest.

I have implemented a Business Dictionary for my latest client using the above methodology, I am happy to share the technical specification for the dictionary and how it interacts with QlikSense if you wish, no this is not a sell job, just would like to see more done around data governance and QlikSense integration to a governance model.

You can email me here at paul.scotchford@awari.com.au

Kind regards

Paul