topic Calculated Dimension Help in QlikView

Calculated Dimension Help

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

Morning guys

I am trying to write a calculated dimension, as per the below. However, I am receiving "//Error in calculation".

Could someone please help where this is going wrong?

=IF(Count({<[FTMR_P2P_KPI]={"Failure","Success","Other"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number]) < 20,'',

Count({<[FTMR_P2P_KPI]={"Failure"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number]))

Many thanks,

Gareth

Re: Calculated Dimension Help

Clever_Anjos — Mon, 23 Oct 2017 10:25:53 GMT

When you use a calculated dimension you are supposed to have a level of aggregation

For example:

AGGR(IF(Count({<[FTMR_P2P_KPI]={"Failure","Success","Other"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number]) < 20,'',

Count({<[FTMR_P2P_KPI]={"Failure"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number])), CUSTOMER)

Re: Calculated Dimension Help

Anonymous — Mon, 23 Oct 2017 10:36:20 GMT

Thanks Clever.

This might not be the method required to achieve what I'm after then.

Essentially I have the a straight table with headings being Rank, Site Code, Site Name, Failure, Success, Other, Total and Success %. Site Code and Site Name are Dimensions, the rest of the columns are Expressions.

I am trying to get rows where the Total column is less than 20. The Total column being the sum of the Failure, Success and Other columns.

Any ideas?

Thanks,

Gareth

Re: Calculated Dimension Help

Clever_Anjos — Mon, 23 Oct 2017 10:38:14 GMT

Please test using your expression as the very one first expression and set "Dimension Limits" to filter your results