I'd like to use the Aggr() function with conditional dimensions. I'm creating a report where a user can dynamically select which dimensions they want to include in their export. Each time they add a dimension, the aggr() function needs to update to include that dimension (or the expression won't calculate correctly).
The expression will always remain the same, for example, avg(SpendAmount). Let's say we have three dimensions to choose from - Dimension1, Dimension2, and Dimension3. If they user only selects Dimension1, only that dimension needs to be included in the Aggr() expression. If they choose all three, we need to include Dimension1, 2, and 3. I'm using an expression as follows:
What if I want to show the user a "Clean" version of the KPI in their list box selector, but the dimensions included in the Aggr() function need to be the original field names? For instance:
Load * Inline [
KPI1, Clean Dimension 1
KPI2, Clean Dimension 2
KPI3, Clean Dimension 3
If I put the KPI_Clean in the list box, GetFieldSelections() won't give me the original KPIs I need. Is there a way to allow them to select the clean names but return the original names to my aggr() statement?
Ok, here's my newest issue: I already have a table, DATA, which houses all of my load data from a source file and which contains the dimensions I'll want to use. I've created two separate Inline Load tables - DIMENSIONS and EXPRESSIONS, which I map to a unique key and then tell my custom table to show or hide based on the key, like below. If a user selects the "Spend (Check)" expression from the list box, I have an expression created in my custom table that says "if(A gets selected, show CheckSpend calculation)."
LOAD * INLINE [metrics_, metric_flag
Spend (Check), A
Spend (Invoice), H
Spend (Firm Only), C
Spend (Client Only), D
Spend (Capital Only), F
% Billable, B
# Payees, E
Since both my DIMENSION and EXPRESSION table are unlinked/isolated tables, I am unable to use any variation of GetFieldSelections() or Concat() in my Aggr() statement. GetFieldSelections() only adds the data structure from my unlinked DIMENSION table, and even though that table contains fields with the same name as those of my data table, the aggr() function (I think) is trying to access data associated with the orphan DIMENSION table. Can I get around this in some way? Does that make sense?