topic Re: Pivot Table in QlikView

Pivot Table

Anonymous — Tue, 14 Oct 2014 08:02:44 GMT

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

Re: Pivot Table

sushil353 — Tue, 14 Oct 2014 08:07:56 GMT

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

Re: Pivot Table

Tue, 14 Oct 2014 08:08:47 GMT

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)

Re: Pivot Table

mdmukramali — Tue, 14 Oct 2014 09:03:23 GMT

Dear,

Kindly find the attached file.

May there is better approach than mine solution.

Thanks,

Mukram

Re: Pivot Table

Tue, 14 Oct 2014 11:42:13 GMT

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.