Conditional Aggr()

[#1]

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?

[MK_QSL · Accepted solution]

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

[MK_QSL]

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

[#4]

Ahh I think this should work! Thank you! Let me test this solution and I'll come mark the thread.

[#5]

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?

[simenkg]

You can try to use Concat(KPI,',') in stead.

[#7]

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?