Evaluate expression based on field value

[#1]

Hi everyone,

I have a table that has the following information:

eMetric          Metric                 AggregationType

CustomerID UniqueCustomer COUNT DISTINCT

Profit         Profit                 SUM

Sales         Sales                 SUM

I want to create a straight table (or pivot) that evaluates the KPI based on its definition in Metric and AggregationType columns.

Is it possible to achieve this?

I tried using the following expression with little success: SUM($(eMetric))

Expected Output:

KPI                  Value

CustomerID     4,520    (evaluates as COUNT(DISTINCT CustomerID) )

Profit                65,900   (evaluates as SUM(Profit) )

Sales               15,520,134     (evaluates as SUM(Sales) )

A sample QVW is also attached.

Many thanks for your help.

[pathiqvd]

Hi,

     Try this you will get,

In Add calculated dim:- =ValueList('customer','sales','profit')

In expr:-

if(ValueList('customer','sales','profit')='customer',count(distinct customerid),

  if(ValueList('customer','sales','profit')='sales',sum(sales),

     if(ValueList('customer','sales','profit')='profit',sum(profit))))

Regards,

[marcus_sommer]

It's not possible in this way - you will always need to pick the right expression in a defined context like the if-loop in the example above or by a larger number of expressions with a pick(match()) lookup and this could be also automated in some way. To the backgrounds of it take a look here: and read also the comments and links there.

- Marcus