topic Re: Remove row from average in expression in QlikView

Remove row from average in expression

jenmclean — Mon, 15 Aug 2016 16:28:49 GMT

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.

Year	Month	KEY_Store	Date	KEY_Report	KEY_FormVer	FORM_Version	Amount
2016	Aug	128	8/2/2016	122	175	9	30.00	Correct
2016	Aug	128	8/3/2016	122	175	9	0.00	Wrong
2016	Aug	128	8/9/2016	122	175	9	30.70

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

Thanks!

Jennie

Re: Remove row from average in expression

Gysbert_Wassenaar — Mon, 15 Aug 2016 16:40:38 GMT

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.

Re: Remove row from average in expression

sunny_talwar — Mon, 15 Aug 2016 16:41:13 GMT

May be this:

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