Expression Pivot table

[#1]

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

[#2]

Can someone please help me with this task?

[#3]

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

[avinashelite]

hi you want average of what??? premium

[#5]

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

[avinashelite]

Please find the attachment for the solution !

[#7]

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

[#8]

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

[avinashelite]

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

[#10]

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