Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jenmclean
New Contributor III

Remove row from average in expression

The following is an expression for calculating average food cost. This data is entered once a week.

avg({$<KEY_FormID={'DRPOS'}, KEY_Report={122}, KEY_Store-={325,210}>}Amount)/100

For one store, they had to back out the data and re-enter it which results in an extra line (noted as Wrong) which is then throwing off my average for not only this store but other stores using that same formula as it is one large chart with 20 + stores in it. All other stores show two dates, while this one has three.

YearMonthKEY_StoreDateKEY_ReportKEY_FormVerFORM_VersionAmount
2016Aug1288/2/2016122175930.00Correct
2016Aug1288/3/201612217590.00Wrong
2016Aug1288/9/2016122175930.70

How can I factor out the line with the 0.00 amount?

Thanks!

Jennie

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Remove row from average in expression

Perhaps like this: avg({$<Amount={'<>0'},KEY_FormID={'DRPOS'}, KEY_Report={122}, KEY_Store-={325,210}>}Amount)/100


If not then you need to think up a way that identifies the wrong records 100% correctly.


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Remove row from average in expression

Perhaps like this: avg({$<Amount={'<>0'},KEY_FormID={'DRPOS'}, KEY_Report={122}, KEY_Store-={325,210}>}Amount)/100


If not then you need to think up a way that identifies the wrong records 100% correctly.


talk is cheap, supply exceeds demand

Re: Remove row from average in expression

May be this:

Avg({$<KEY_FormID={'DRPOS'}, KEY_Report={122}, KEY_Store-={325,210}, Amount -= {0}>}Amount)/100