Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
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:
sum( Aggr(avg(SpendAmount),
if(SubStringCount(Concat(dimension_flag, '|'), 10), Dimension1),
if(SubStringCount(Concat(dimension_flag, '|'), 11), Dimension2),
if(SubStringCount(Concat(dimension_flag, '|'), 12), Dimension3));
The SubStringCount(...) is just to figure out if the user has selected the dimension or not. I know that portion works correctly.
When I select dimensions, the expression will not calculate. Is there any way to do what I want?
Create an Inline Table like below
Load * INLINE
[
KPI
Dimension1
Dimension2
Dimension3
];
Create a List Box of KPI and use below expression
SUM(Aggr(avg(SpendAmount),$(=GetFieldSelections(KPI))))
Create an Inline Table like below
Load * INLINE
[
KPI
Dimension1
Dimension2
Dimension3
];
Create a List Box of KPI and use below expression
SUM(Aggr(avg(SpendAmount),$(=GetFieldSelections(KPI))))
Consider you have below table
Sales:
Load * Inline
[
Country, Brand, Sales
UK, A, 100
UK, B, 120
UK, C, 130
GERMANY, B, 200
GERMANY, C, 210
POLAND, A, 190
POLAND, B, 400
];
Now Create an Inline table with your desired dimensions
KPI:
Load * Inline
[
KPI
Brand
Country
];
Create a List Box with KPI as Dimension
To find out the Max Sales per selected Dimensions....you can use below expression...
=max(Aggr(SUM(Sales),$(=GetFieldSelections(KPI))))
Ahh I think this should work! Thank you! Let me test this solution and I'll come mark the thread.
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 [
KPI, KPI_Clean
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?
You can try to use Concat(KPI,',') in stead.
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)."
Expressions:
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?