topic Variance / Tolerance Expressions - Flagging in QlikView

Variance / Tolerance Expressions - Flagging

Mon, 03 Jun 2013 19:21:42 GMT

I am trying to develop an expression that will look at the percentage of the difference between current month and previous month charges and compare to a table, then the result should be identified in a way that if the difference in the charge is 10% (or 5% or 20%) from one month to the next it will flag that charge id.

Any ideas?

Here is an example.

This is my tolerance table:

This is my chart -

The "DIFF in %" on my chart is -10% .... How do I "FLAG" that??

Thanks in advance for your help!

Re: Variance / Tolerance Expressions - Flagging

Tue, 04 Jun 2013 17:03:23 GMT

Dawn,

If you might have multiple ranges for each ChargeID then you could use IntervalMatch to compare the percent difference to the range you're interested in. If you only ever have one range (record) for each ChargeID then you could just join the tables and do a straight compare. I assume you'd assign the flag to the result field (maybe set it to a 1 if the flag is set).

Once you have the result, if you'd like a visual indicator based upon the flag, you can add an expression to the color propery of the column to change the background. E.G..... Expand the plus sign next to the field, click "background color", click the Edit button, then enter into the Definition Box something like...

if(RESULT = 1, RGB(234,21,21))

That would change the background to red.

Hope this helps.

Re: Variance / Tolerance Expressions - Flagging

Tue, 04 Jun 2013 17:09:12 GMT

So, if you'd like the ChargeID to turn red, the do that to the dimension for ChargeID. If you want the Current vs. Previous Month to turn red then do it for that expression (field). etc....

Re: Variance / Tolerance Expressions - Flagging

Wed, 05 Jun 2013 14:10:55 GMT

Looks like you have one set of ranges per ChargeIDNum. In this case, you don't need IntervalMatch. All you need to do is join the table with the ranges to the original table. This will give you one record with everything you need. Then all you need to do is write the code to test the condition and highlight the appropriate field.

Re: Variance / Tolerance Expressions - Flagging

Wed, 05 Jun 2013 14:11:41 GMT

Thanks so much - that's what I needed!

Re: Variance / Tolerance Expressions - Flagging

Wed, 05 Jun 2013 14:12:02 GMT

Thanks so much - that's what I needed!