9 Replies Latest reply: Oct 2, 2015 2:08 AM by

# help with pivot table

Hi,

I have a problem with a pivot table... in particular I have this rows of data (example below):

ManagerISOProductNet WeightImport Euro
RossiCLApple125
RossiCLApple137
RossiITPere102

And I create this Pivot Table with these colums:

ManagerISOProductTotal Net Weight I° CatTotal ImportAverage PriceTotal Net Weight II° Cat

where the dimensions are:

Manager, ISO and Product

and the Expressions are:

Total Net Weight = sum(Net Weight)

Total Import = sum(Import Euro)

Average Price = sum(Import Euro)/sum(Net Weight)

The problem is How to calculate the column named Total Net Weight II° Cat...I would like to calculate the sum of kilograms where the average price of every single row is less than 50 % of the average price of the product calculated in column Average Price.

I try to calculate with this expression bat I think isn't correct...

Total Net Weight II° Cat = sum(if([Import Euro]/[Net Weight] < ( aggr( sum(Import Euro)/sum(Net Weight)/2), Manager,ISO,Product  ), Net Weight, 0)

In particular I do not know how to set up an expression that allows to evaluate ( and include it in the sum ) every single detail line based on expressions already calculated previously as aggregation

Michele

Messaggio modificato da Michele Becchio

• ###### Re: help with pivot table

Can you post a small qlikview document that demonstrates the problem?

• ###### Re: help with pivot table

Hi, I am creating a new discussion with attachment because this reply doesn't permit to add an attachment

• ###### Re: help with pivot table

ok!, I have attached the sample qvw.

Thank you

Michele

• ###### Re: help with pivot table

Try this expression:

sum({<RowID={"=2*sum(TotaleRicavoNettoScGoldenConsolidato)/sum(TotaleKgNettiFatturatiClienteConsolidato) <sum(total <NomeGruppoProdottoStatistico> TotaleRicavoNettoScGoldenConsolidato)/sum(total <NomeGruppoProdottoStatistico> TotaleKgNettiFatturatiClienteConsolidato) "}>}TotaleKgNettiFatturatiClienteConsolidato)

Do those results make sense?

See attached qvw

• ###### Re: help with pivot table

Yess!!! Perfect!!!

I don't understand How you use RowId in set analysis  and could you please give me an idea of where I can find information regarding this type of use of set analysis ...

Thank you very much!!!!

Michele

• ###### Re: help with pivot table

RowID was available in the data you posted. Since it seems to uniquely identify the individual records it could be used in the set analysis expression for just that purpose.

• ###### Re: help with pivot table

Ok!

Thank you!

I usually use set analysis in this form:

{<fieldName={'value'}>} but never where value is the result of condition of other fields... very interesting

• ###### Re: help with pivot table

HI,

Try like this

create a variable

vAverageValue = Sum(aggr( sum(Import Euro)/sum(Net Weight)/2), Manager,ISO,Product  ))

Now use this expression below

Total Net Weight II° Cat = if(Sum([Import Euro])/Sum([Net Weight]) < vAverageValue , Sum(Net Weight), 0)

Hope this helps you.

Regards,

Jagan.