topic Re: Get dim values from a pivot table and exclude those values in another straight table dimension in QlikView

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

Tue, 12 Jan 2016 11:29:35 GMT

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

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

tresB — Tue, 12 Jan 2016 11:35:12 GMT

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).

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

Tue, 12 Jan 2016 12:03:36 GMT

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

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

tresB — Tue, 12 Jan 2016 12:13:51 GMT

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.

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

Wed, 13 Jan 2016 11:34:27 GMT

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

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

tresB — Wed, 13 Jan 2016 11:57:00 GMT

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.

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

Wed, 13 Jan 2016 13:16:18 GMT

Thanks.

Unfortunately, it doesn't work.