How To Calculate The Achievement in Expression

[mohdhaniff]

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:

[jayanttibhe]

Hi,

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

Thanks

Jayant

[vishsaggi]

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

or

in the expression for achievement use like

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

TRy and let us know.

[Lech_Miszkiewicz · Accepted solution]

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:

1. you can try to remove DISTINCT from your statement - i am not sure what it will bring as i dont know your datamodel
2. 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

[mohdhaniff]

Thanks Lech.

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

[Lech_Miszkiewicz]

No worries - happy to help.

cheers

Lech