9 Replies Latest reply: Feb 2, 2015 8:15 AM by Volha Burak

# Expression Pivot table

Hi,

I have data schema:

I want to do this pivot table:

But I can't implement formula:

AVG(

for each Product

if(Revers = 1)

{

((Last Price in Data range (Year, quarter, month) + Sum Premium in Data range)/(First Price in Data range + Sum Premium Prior Data range)) - 1

}

else

{

1-((Last Price in Data range (Year, quarter, month) + Sum Premium in Data range)/(First Price in Data range))

}

Unfortunately I can not understand how to group the data in Expression and how to work with the pivot table dimensions.

Thanks so much

• ###### Re: Expression Pivot table

Can someone please help me with this task?

• ###### Re: Expression Pivot table

hi you want average of what??? premium

• ###### Re: Expression Pivot table

Not premium. Average for value list, where value is:

for each product in Product group:

If Reverce = 1

((Last Price for product in date range + Sum Premium for product in date range ) / (First Price for product in date range + Sum Premium for product before date range)) - 1

Else

1 - ((Last Price for product in date range + Sum Premium for product in date range ) / (First Price for product in date range))

• ###### Re: Expression Pivot table

Please find the attachment for the solution !

• ###### Re: Expression Pivot table

I want see statistics in pivot table for each year, quarter, month (2004 (Q1, Q2, Q3, Q4), 2005, 2006, 2007). Date range in formula is a dimension in table.

This expression calculates the value directly in the whole interval of dates

• ###### Re: Expression Pivot table

I think function FirstSortedValue([Price Date],[Price Date]) in expression without taking into account the dimension

• ###### Re: Expression Pivot table

Your data is like that only that's why its showing like that

• ###### Re: Expression Pivot table

Ok. Maybe you know how can I change the data to solve the problem?

• ###### Re: Expression Pivot table

Hello,

your formula is not possible in a expresion.

You must create a set analysis as 'sum({\$<PGId={"1"}>}Premium)'

In {\$< you put fields with condition>}.

I join file with a example qvw and set analysis explication.

Sorry for my english.

Julien