How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

[paulyeo11]

Hi All

Table 1 Expression :-

Money(Pick(

Match([Pls select 601st Dim],'sales','eXP','rEVENUE','nET_PROFIT'),

Sum (($(ColumnDim601)/$(Columndim89))/1000),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89)),

$(vMoneyFormatK))

When I create the above pivot report. i notice that when my expression compute with positve value for example sales it have no issue. But when i compute eXP which have negative value it very slow , it there any way i can speed up ? As my issue is if i load the actual data , it will get out of memory error.

Table 2 Expression :-

Sum({<eXP={eXP}>}Amount/1000)/$(Columndim89)

For table 2 i have no issue .

Paul

[paulyeo11]

my QV Doc

[PrashantSangle · Accepted solution]

Hi,

did you try your expression individually ,

like take on straight / pivot table

dimension same as in original chart

and expression

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89)

take [Pls select 601st Dim] in list box

select eXP in list box and how much time it will take to compute??

do same with other selection also check where issue is occurring.

Regards

[paulyeo11]

Hi Sir

I not yet try your proposal . I will try it out soon.

I have been using match(pick for last 6 month for all my chart and table with many type of expression , I encounter issue when field = eXP.

So I know the issue is when value with +ve and -ve have issue.

Paul

Sent from my iPhone

[paulyeo11]

Hi Max

I have try your approach , so i create below in line load script. ( See the last Tab )

LOAD * INLINE [

variable,field

eXP,eXP

nET_PROFIT,nET_PROFIT

rEVENUE,rEVENUE

];

I have forgotten what should be the next step , as too long never use this. can you guide me thru a bit ?

My question is how to display the field ? So that i can select eXP or rEVENUE

Paul

[paulyeo11]

Hi MAx

I manage to figure out . and i found that your approach working fine.

it speed up a lot. i will use your approach.

Paul