Get dim values from a pivot table and exclude those values in another straight table dimension

[#1]

Hi guru's,

I've a problem.

I have a pivot table that shows the sold articles per shop in a specific period.

Pivot Table:

Dim1 : Shop

Dim2 : Cod Art

Expr1 : Sum(qta)

Expr2 : Sum(Sales)

Those dimensions are referred to a table with stock movements.

I have to show in another table all the articles (cod Art) unsold in that specific period.

So all the Cod Art that comes out from the pivot table must be excluded from the whole set of Articles.

To take those unsold articles i have to refer to the Articles tab.

I hope it's clear

Any idea about it?

Thanks in advance

Best regards

[tresesco]

I guess, you should use set analysis for this. try expression like:

Expr1 : Sum({1-$} qta)

Expr2 : Sum({1-$} Sales)

{1-$} - returns sales/qta for everything excluded by the current selection (possibly your Period dimensions).

[#3]

Hi tresesco,

thirst of all thank you so much.

Good idea but i have to generate a second table only with articles that are not inclued in the set came out from the first table.

Any idea?

Best regards

Giampiero

[tresesco]

If I can assume that, your first chart/table is showing data w.r.t current selection, writing expression as I suggested above would give your desired output. If any doubt, try to upload a sample qvw and state your expected output there.

[#5]

Hi,

Thank you again and I'm sorry if I'm late to reply to you.

I can't produce a qvw.

I'll try to explain better my problem.

I have a pivot table that shows the sold articles per shop in a specific period.

(I have two vars to set the period)

My Pivot Table:

Dim1 : Shop

Dim2 : Cod Art

Expr1 : Sum(qta)

Expr2 : Sum(Sales)

Expr1:

sum({$<dat_doc={">=$(vStartDate) <= $(vEndDate)"}>} Tab_MOVSTK.qty)

Those dimensions are referred to a table with stock movements (Tab_MOVSTK).

I have the Article table (with cod.Art, desc. Art)

Supposing we have in Tab_Article these records:

001 glass

002 dish

003 table

004 window

and my pivot table give to me this result:

Shop        Cod.Art     Qty     Sales

Shop1      001          10          200€

Shop1      002           5           15€

Shop2      002           20          60€

I'd like to have another table like this:

Cod Art          Desc Art

003                Table

004                Window

The unsold articles.

Thanks in advance

Regards

Giampiero

[tresesco]

Try like:

take straight table.

Dim1: Cod Art 

Dim2: Desc Art

Exp:  sum({$<dat_doc={"<$(vStartDate) > $(vEndDate)"}>} Tab_MOVSTK.qty)

Now, in the presentation tab, hide the expression column.

[#7]

Thanks.

Unfortunately, it doesn't work.