Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qvraj123
Contributor II

get total - tricy and confusing

Hi All,

Thanks a lot for your time, I have this scenario - where I need to get the total of average assets from the below screenshot I need

to get 1.5 / 2.6 and 1.5/4.5 and 1.5/3.4 etc

average assets =

[Total Assets] /
(
IF( EMPLEVEL_CALC = 'Non-Employee' OR (SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA'),
SUM( AGGR(COUNT(IF( EMPLEVEL_CALC = 'Non-Employee',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO)),
IF( EMPLEVEL_CALC <> 'Non-Employee',
SUM(AGGR(COUNT(IF(SOURCE='HRIS_DATA',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO))
))
)

Average Assets% = for each record 1.5/ each value like 2.6 and 4.5 and so on - stuck with getting 1.5

[Total Assets] =

SUM( AGGR(COUNT(IF(SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO))

screenshot.png
thanks a lot

5 Replies
qvraj123
Contributor II

Re: get total - tricy and confusing

I am sorry got confused it should be 2.6 / 1.5 and 4.5 / 1.5 - In my previous message I have given the wrong order

thanks a lot for your time

Raj

Not applicable

Re: get total - tricy and confusing

See attached example. You can use the same logic. Would be helpful if you could attach a sample.

Regards

Koushik Vutha.

qvraj123
Contributor II

Re: get total - tricy and confusing

This is my complete expression to get the average assets - once I get this I need to / by 1.5 for each value

SUM( AGGR(COUNT(IF(SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO)) /
(
IF( EMPLEVEL_CALC = 'Non-Employee' OR (SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA'),
SUM( AGGR(COUNT(IF( EMPLEVEL_CALC = 'Non-Employee',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO)),
IF( EMPLEVEL_CALC <> 'Non-Employee',
SUM(AGGR(COUNT(IF(SOURCE='HRIS_DATA',EMPNO)),SOURCE,EMPLEVEL_CALC,EMPNO))
))
)

Highlighted
qvraj123
Contributor II

Re: get total - tricy and confusing

Hi Koushik, thanks a lot

attached the sample QVW file - for average assets% = I need to divide the each value like 2.6 with the 1.5 value

thanks again

qvraj123
Contributor II

Re: get total - tricy and confusing

Hi Koushik,

any suggestions, I looked at the test file from you, unlike your calcs  mine are not straight forward

thanks for your time

Raj