8 Replies Latest reply: Mar 21, 2013 9:28 AM by Annemarie Wagelaar

# Multiple expressions in an expression field in a pivot table

Hello,

I would like to create a pivot table like this

KPICurrent yearLast yearGrow%
Total sales120.000100.00020%
Total toy sales40.00050.000-20%
Number of customers13.00010.00030%
Average sale per customer 9,210-8%

I created a dimension like this:

=valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

That works.

But how do I get the expression fields filled? I was thinking of building the expressions Current Year and Last Year each containing 4 expressions. But how do I formulate this?

Or is there a better solution?

Thanks for thinking with me.

Tariaj

• ###### Re: Multiple expressions in an expression field in a pivot table

I'd rather use straight table here.  No much difference in this case.  As for expressions, the "Current Year" may look like this:

if(valuelist(...)='Total sales', <expression for total sales curr year>,
if(valuelist(...)='Total t sales', <expression for total t sales curr year>,
if(valuelist(...)='Number of customers', <expression for number of customers curr year>,
if(valuelist(...)='Average sale per customers', <expression for avg sale per customer curr year>
))))

Similar expression for the last year.

the third column can be calculated using the prevous two, e.g.
([Current Year] - [Last Year])/[Last Year]

(where Current Year and Last Year are the labels of the first two expressions)

Regards,
Michael

• ###### Re: Multiple expressions in an expression field in a pivot table

Thanks a lot. It works!

• ###### Re: Multiple expressions in an expression field in a pivot table

Hi,

It may be helpful if you can provide a sample set of data.

Regards,

• ###### Re: Multiple expressions in an expression field in a pivot table

Thank you for your offer to help. As you can see above Michael gave me a solution. Regards.

• ###### Re: Multiple expressions in an expression field in a pivot table

Hi tariaj68,

You can create different expressions for each line using the pick function. For each line you should define a specific set analysis expression.

pick( RowNo(),

sum(Total Sales),

sum({< Toy = {1} >}Sales),

Count(Distinct Customer),

avg(Sales)

)

You dimension is correct as valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

Hope this helps you.

Regards

• ###### Re: Multiple expressions in an expression field in a pivot table

Hi,

That really depends on what you have in your data. Anyway it's better if you prepare you data for calculation, i.e. have a KPI dimension and values calculated in a load script otherwise you risk to have performance issues (highly significant for huge volumes) and need to think out magic formulas.

With the calculated dimension you can use the following expression:

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Total sales',

1,

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Total toy sales',

2,

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Number of customers',

3,

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Average sale per customer',

4,

))))

You'll have:

Just paste your formulas instead of 1-4, don't forget to add set analisys for LFL comparison. But it's better to think about (re-)designing of data.

Best regards,

Maxim

• ###### Re: Multiple expressions in an expression field in a pivot table

Hi,

You may create a Inline table like:

[KPIID,KPIMatrix

1,'Total Sales'

2,'Total toy sales'

3,'Number of customers'

4,'Average sale per customer'];

In chart expression:

Exps1(CurrentYear):

If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<CurrentYear>,

If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<CurrentYear>,

If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<CurrentYear>,

Expr_for_AvgSalePerCustomer<CurrentYear>

)

)

)

Exps2(Last Year):

If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<LastYear>,

If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<LastYear>,

If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<LastYear>,

Expr_for_AvgSalePerCustomer<LastYear>

)

)

)

Exps3(Growth%):

(Exps1-Exps2) / Exps1

Regards,

Som

• ###### Re: Multiple expressions in an expression field in a pivot table

I tried them all and they all work.

Finally I applied the solution of Jeff Martins using the pick( RowNo()) because in that way the expressions were the shortest and I had the feeling the performance also was the best.

I am thinking of moving some expressions to the load script as Maxim Senin recommended.

Regards,

Tariaj