PIVOT TABLES WITH EXPRESSIONS

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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 ?

[#5]

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.

[#6]

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

[#7]

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.