topic Re: Expression Pivot table in QlikView

Expression Pivot table

Mon, 26 Jan 2026 18:19:17 GMT

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

Mon, 02 Feb 2015 08:48:36 GMT

Can someone please help me with this task?

Re: Expression Pivot table

Mon, 02 Feb 2015 10:03:34 GMT

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

Re: Expression Pivot table

avinashelite — Mon, 02 Feb 2015 10:08:34 GMT

hi you want average of what??? premium

Re: Expression Pivot table

Mon, 02 Feb 2015 11:03:57 GMT

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

avinashelite — Mon, 02 Feb 2015 12:01:33 GMT

Please find the attachment for the solution !

Re: Expression Pivot table

Mon, 02 Feb 2015 12:26:48 GMT

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

Mon, 02 Feb 2015 12:30:35 GMT

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

Re: Expression Pivot table

avinashelite — Mon, 02 Feb 2015 12:59:45 GMT

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

Re: Expression Pivot table

Mon, 02 Feb 2015 13:14:11 GMT

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