topic How To Calculate The Achievement in Expression in QlikView

How To Calculate The Achievement in Expression

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Currently I'm having problem to calculate the 'Achievement' column in expression: -

Column 1 : December

sum({$<[Date Disbursed - Month]={'$(=max([Date Disbursed - Month]))'}>} [ETP Amount]))

Column 2 : Target

sum(DISTINCT if(index([Make Segment II],'New Nation')>0,120000,
if(index([Make Segment II],'New Foreig')>0,120000,
if(index([Make Segment II],'Used')>0,120000,
if(index([Make Segment II],'Recondi')>0,40000,
if(index([Make Segment II],'Other')>0,0,
if(index([Make Segment II],'Enterpr')>0,0,0)))))))

Column 3 : Achievement

column (1) / column (2)

Expected Result:

Actual Result:

Re: How To Calculate The Achievement in Expression

jayanttibhe — Wed, 21 Dec 2016 02:38:35 GMT

Hi,

Can you please sample app ? Also, Target Column in Example and Result is not matching. You need to correct it first.

Thanks

Jayant

Re: How To Calculate The Achievement in Expression

vishsaggi — Wed, 21 Dec 2016 03:06:18 GMT

‌Go to number tab in the properties and use integer with relative checkbox checked...

in the expression for achievement use like

= Num((col1/col2), '#0') & '%'

TRy and let us know.

Re: How To Calculate The Achievement in Expression

Lech_Miszkiewicz — Wed, 21 Dec 2016 03:37:10 GMT

Hi,

It seems to work properly - considering your input values are correct. Arent they?

Dec 2016 - showing values in milions and Target is only in hundreds of thousands. So % seems to be ok.

Now the total value. In your expression you are using Distinct which means it takes only unique values to calculate it.

That beeing said from your total on Dec 2016 you get 76769086 and distinct values for target are 120000 and 40000 giving you total result for Target 160000. Column (1)/Column(2) gives you 76769086/160000 = 479806.788 which is exactly what you are getting. So the QlikView caluclation works as designed

sum(DISTINCT if(index([Make Segment II],'New Nation')>0,120000,
if(index([Make Segment II],'New Foreig')>0,120000,
if(index([Make Segment II],'Used')>0,120000,
if(index([Make Segment II],'Recondi')>0,40000,
if(index([Make Segment II],'Other')>0,0,
if(index([Make Segment II],'Enterpr')>0,0,0)))))))

Now. To get it working your way i would suggest:

you can try to remove DISTINCT from your statement - i am not sure what it will bring as i dont know your datamodel
or i would suggest and recommend to load targets during script execution as actual values, instead of hardcoding it in your expression which can be tricky, You can just load targets on hight level linked to your field [Make Segment II]

Hope this helps

regards

Lech

-------------------------------------------------------------------------------------------------------------

Please marked correct answers as CORRECT

Re: How To Calculate The Achievement in Expression

mohdhaniff — Wed, 21 Dec 2016 04:29:56 GMT

Thanks Lech.

My fault.. the target should be in Million...

Re: How To Calculate The Achievement in Expression

Lech_Miszkiewicz — Wed, 21 Dec 2016 04:35:52 GMT

No worries - happy to help.

cheers

Lech