topic Re: Evaluate expression based on field value in QlikView

Evaluate expression based on field value

Tue, 03 Jan 2017 10:24:11 GMT

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.

Re: Evaluate expression based on field value

pathiqvd — Tue, 03 Jan 2017 10:29:55 GMT

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,

Re: Evaluate expression based on field value

marcus_sommer — Tue, 03 Jan 2017 10:57:28 GMT

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