topic Pivot table in QlikView

Pivot table

m4u — Sun, 21 Nov 2010 12:10:07 GMT

Hi all,

i create a pivot table like in the attached image,

the item category and "month" are used as dimensions, and i have few expressions for the total of each year.

i need to add another column on the end, to show the "ytd" value of each year.

how can i do that without adding dummy rows to my table with the value 'ytd' in month name?

Pivot table

Sun, 21 Nov 2010 12:56:18 GMT

yes you can

Pivot table

m4u — Sun, 21 Nov 2010 13:08:48 GMT

Pivot table

Sun, 21 Nov 2010 22:44:54 GMT

add cyclic with month and ytd to the pivot

Pivot table

Miguel_Angel_Baeyens — Sun, 21 Nov 2010 22:52:20 GMT

Hello Shlomo,

One way to get this is creating a new field in your master calendar called YTD so

InYearToDate(TempDate, Date($(vMaxDate)), 0) AS YTDFlag

Where TempDate is the date field you are using to build the calendar and vMaxDate is the maximum date in the data model.

then you can add a new expression

Sum({< YTDFlag = {'-1'} >} SalesAmount)

Which will return the sum you are looking for. since you are using Month as dimension, and this is a new expression, you will get a new column within each month.

As this will get the chart with a lot of redundant information, I'd create one expression for each month and a new one for the YTD, then remove "Month" from dimension, say for month

Sum({< Year = {2010}, Month = {1} >} SalesAmount)

and for YTD

Sum({< YTDFlag = {'-1'} >} SalesAmount)

Using the YTDFlag field as created above. I know it's more arduous but I don't see any other solution as to have month and YTD sharing the same dimension.

Hope this helps

Pivot table

m4u — Mon, 22 Nov 2010 09:03:41 GMT

thanks for answering. shlomo