topic Re: Expression Question in QlikView

Expression Question

danielnevitt — Tue, 19 Nov 2013 15:54:53 GMT

Hi,

I am trying to match costs/revenues based on the following logic:

Match 'Cost (Payable)' with 'Revenue (Receivable)' (field COST_REVENUE_IND) on the same COST_CD and EXTENDED_AMT.

Also is it possible to match on EXTENDED_AMT (with a 10% variance)?

Lastly I would also like to be able to restrict the straight table to only show records where the % Variance from Mean is a certain % say 100%

Any pointers as to how to approach the above will be much appreciated.

Thanks,

Daniel

Re: Expression Question

datanibbler — Tue, 19 Nov 2013 16:03:51 GMT

Hi Daniel,

it's all in one table, is it?

Well, I would consider splitting it into two tables (by filtering on COST_CD and EXTENDED_AMT) and then joining on those fields - or sorting the table by those two fields.

=> With the first approach (joining) and a variance, join on only COST_CD and put EXTENDED_AMT (with the 10%) in a WHERE_clause.

=> When you stick with one table and sort it, you can use the PREVIOUS() function to calculate the variance.

HTH

Best regards,

DataNibbler

Re: Expression Question

danielnevitt — Tue, 19 Nov 2013 18:17:15 GMT

Hi DataNibbler,

Thank you for your reply.

Are you able to post an example of what you mean?

Ideally I want to show pairs of data where the EXTENDED_AMT and COST_CD are the same, but the COST_REVENUE_IND are different. Hope that makes sense.

Thanks,

Daniel