topic PIVOT TABLES WITH EXPRESSIONS in QlikView

PIVOT TABLES WITH EXPRESSIONS

Mon, 19 Jul 2010 10:32:16 GMT

I have a pivot table with two expressions :

- sales per month

- stock (not per month)

I want to put the stock expression outside the pivot month dimension ?

For example :

PRODUCTS STOCK MONTH1 MONTH2 MONTH3

product A 100 10 20 15

PIVOT TABLES WITH EXPRESSIONS

Mon, 19 Jul 2010 16:55:48 GMT

When loading the data you will need to create a summary stock table, something like the following.....

stock:

select

product,

sum(stock_qty) as tot_stock_qty;

FROM table stock_details

group by product;

now in your pivot table properties go to dimensions - select "Add Calculated Dimension", formula is = tot_stock_qty.

This allows you to display the total outside the month on a pivot table.

PIVOT TABLES WITH EXPRESSIONS

Mon, 19 Jul 2010 22:31:03 GMT

A calculated dimension seems like your best bet, but you may be able to do it without modifying the load.

I was able to set up a calculated dimension and then use Aggr() to evaluate it. Try:

Aggr(Sum(STOCK), PRODUCTS)

Replace Sum(STOCK) with whatever you are using for your stock expression.

PIVOT TABLES WITH EXPRESSIONS

Tue, 20 Jul 2010 08:12:12 GMT

Problem is that I have several fields in my dimension namely :

- products fields (Quality / Design/ Color / ...)

- month

My expressions are :

- stock

- sales

Result is that I have a stock line for every month.

I want only on stock figure ?

PIVOT TABLES WITH EXPRESSIONS

Wed, 21 Jul 2010 17:17:27 GMT

Did you put the calculated dimension before Month on the list? If you place it after month, then you would get one for each month. If you place it before, you should get one for each record, but not each month.

Is it a pivot with Month across the top?

I've attached a very simplified example.

PIVOT TABLES WITH EXPRESSIONS

Thu, 22 Jul 2010 08:27:25 GMT

OK but my dimensions for product exist out of 4 fields. How to aggregate ?

PIVOT TABLES WITH EXPRESSIONS

Thu, 22 Jul 2010 17:10:37 GMT

Just keep adding parameters to the Aggr function. For example:

Aggr(Sum(STOCK), PRODUCTS, OTHER, SELLER, THISDIM)

For the Aggr function, the first parameter is the expression and any subsequent parameters are dimensions to aggregate by.

I added the same sample with 2 dimensions before the calculated dimension. It should work the same for four.