topic How to change dimension in Aggr function using Cyclic Group in expression? in QlikView

How to change dimension in Aggr function using Cyclic Group in expression?

Mon, 23 Apr 2012 04:06:21 GMT

I have a cyclic group and it has some of dimension such as KODYA,KECAMATAN,SUBBRANCH, and BRANCH.

This is my code in my expression :

if(YEAR_TRX = Max({<YEAR_TRX = {$(=Max(YEAR_TRX))}>} YEAR_TRX),

(Column(1) /

Aggr(NODISTINCT

Sum({$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'}>}

$(vActual)),

if(GetCurrentField("KecamatanGroup") = 'KECAMATAN', KODYA,

if(GetCurrentField("KecamatanGroup") = 'KODYA', SUBBRANCH,

if(GetCurrentField("KecamatanGroup") = 'SUBBRANCH', BRANCH,

if(GetCurrentField("KecamatanGroup") = 'BRANCH', BRANCH,

))))

)) * 100, 0)

Can someone explain to me why can't code work properly? This total result alwas 0.

I need to change my cyclic group "KecamatanGroup" so the value always be change.

Thanks

How to change dimension in Aggr function using Cyclic Group in expression?

lironbaram — Mon, 23 Apr 2012 07:00:23 GMT

hi i dont think you can use a formula in the aggr part so try to replace

if(GetCurrentField("KecamatanGroup") = 'KECAMATAN', KODYA,

if(GetCurrentField("KecamatanGroup") = 'KODYA', SUBBRANCH,

if(GetCurrentField("KecamatanGroup") = 'SUBBRANCH', BRANCH,

if(GetCurrentField("KecamatanGroup") = 'BRANCH', BRANCH,

))))

with a variable which will hold the result of the formula above

How to change dimension in Aggr function using Cyclic Group in expression?

d_pranskus — Mon, 23 Apr 2012 07:13:03 GMT

Instead of using if statements with GetCurrentField function, just use group name.

if(YEAR_TRX = Max({<YEAR_TRX = {$(=Max(YEAR_TRX))}>} YEAR_TRX),

(Column(1) /

Aggr(NODISTINCT

Sum({$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'}>}

$(vActual)), group_name

)) * 100, 0)

How to change dimension in Aggr function using Cyclic Group in expression?

Mon, 23 Apr 2012 08:41:03 GMT

I have use a variable but it can't work when i change dimension into cyclic.

vGroup =

if(GetCurrentField("KecamatanGroup") = 'KECAMATAN', KODYA,

if(GetCurrentField("KecamatanGroup") = 'KODYA', SUBBRANCH,

if(GetCurrentField("KecamatanGroup") = 'SUBBRANCH', BRANCH,

if(GetCurrentField("KecamatanGroup") = 'BRANCH', BRANCH,

))))

But when i change my Aggr function, it can't work.

How to change dimension in Aggr function using Cyclic Group in expression?

d_pranskus — Mon, 23 Apr 2012 09:05:20 GMT

Try this

if(YEAR_TRX = Max({<YEAR_TRX = {$(=Max(YEAR_TRX))}>} YEAR_TRX),

(Column(1) /

Aggr(NODISTINCT

Sum({$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'}>}

$(vActual)),

$(=if(GetCurrentField("KecamatanGroup") = 'KECAMATAN', 'KODYA',

if(GetCurrentField("KecamatanGroup") = 'KODYA', 'SUBBRANCH',

if(GetCurrentField("KecamatanGroup") = 'SUBBRANCH', 'BRANCH',

if(GetCurrentField("KecamatanGroup") = 'BRANCH', 'BRANCH',

)))))

)) * 100, 0)

Darius