Pivot Table

[Anonymous]

Hi

I have table like this

Month	Dim	Vals
July	Custom brokers	10
July	Duty	20
July	Freight inbound	30
July	Freight outbound	40
July	Logistic overhead	50
July	Net Revenues	1000
July	Other	500
July	Warehousing	26
Aug	Custom brokers	20
Aug	Duty	30
Aug	Freight inbound	40
Aug	Freight outbound	50
July	Logistic overhead	60
Aug	Net Revenues	2000
Aug	Other	1500
Aug	Warehousing	32

and i have to pivot table and it show all items except "other' and "net revenues and each value should be divided by net revenue month wise as sample below.

Dim	July	Aug
Custom brokers	'=10/1000	'=20/2000
Duty	"=20/1000
Freight inbound	"=30/1000
Freight outbound	"=40/1000
Logistic overhead

I am new to qlikview. can you please help on this

regards

ramu

D

[sushil353]

Hi,

It is simple to make it in pivot table.

add dimension Dim as first dimension and Month as second.

Then write expression as Sum({<Dim-={'Other','Net Revenues'}>}Vals)/Sum({<Dim={'Net Revenues'}>}Vals)

Now hold the month dimension and move it over the expression in pivot table.

HTH

Sushil

[#3]

Hi,

You can use a calculated dimension if(Dim='Other' or Dim = 'Net revenue',null(),Dim) and select suppress when value is null.

In the expression sum(Vals)/Sum({<Dim={'Net Revenue'}>}Vals)

[mdmukramali]

Dear,

Kindly find the attached file.

May there is better approach than mine solution.

Thanks,

Mukram

[#5]

Hey Ramu,

    You can drag month column to horizontal.

    And in expression

sum({<Dim<>{'Net Revenue'} and Dim<>{'others'}}Vals) / (sum({<Dim={'Net Revenue'}Vals))

It will work

And fulfill your requirement.