Methodology questions (nested if vs. set analysis) + Avg Reference Line

[mellerbeck]

Sorry this is a couple questions in one. But the first that I have been wondering lately is say I have

LOT      QTY     WEIGHT     TRAN_DATE     WHS

a1          1000     1               1/1/2011          A

b1          1000     1               1/1/2011          B

And I want to create a chart that only shows the QTY * Weight for the last 7 days for WHS A I typically do a somewhat crazy nested if like this

sum(if(TRAN_DATE > vNewDate - 7,

if(WHS='A',QTY * WEIGHT)))

It feels like I should maybe be using set Analysis for this sort of thing? Any thoughts, is there a better/different/easier way?

----

I am also trying to figure out how to do an Average Reference Line. Lets say

LOT	QTY	WEIGHT	TRAN_DATE	WHS
a1	1000	1	1/1/2011	A
a1	-1000	1	1/1/2011	A
b1	1000	1	1/2/2011	A
c1	1000	1	1/3/2011	A
c2	1000	1	1/3/2011

The correct average should be

3000 / 3 or 1,000  (don't count 1/1/2011 because a net total of zero was produced )     

So to get the average I think I would need to sum the QTY for the day, and then divide by the days that don't have a zero.

I started out trying the below but of course it doesn't work because it still would count the day that has zero..

=Count (DISTINCT if(TRAN_DATE> vNewDate -7 AND TRAN_DATE <=vNewDate,

if(WHS='A', TRAN_DATE

)))

Thanks for any comments!

[mellerbeck]

OK, I think I got it

=Sum(

if(TRAN_DATE>vNewDate -7 AND TRAN_DATE <=vNewDate,

if(WHS='A',QTY))) /

(count(if(TRAN_DATE > vNewDate-7 AND TRAN_DATE <=vNewDate,

if(WHS='A',

if(aggr(sum (QTY), TRAN_DATE) > 0, TRAN_DATE)))))

Anyone want to proof it for me feel free (or suggest a better way!)

[Jason_Michaelides]

The first one's pretty straightforward I think:

SUM({<WHS={A}, TRAN_DATE={">vNewDate-7"} >}  QTY*WEIGHT)

The average is a kittle more complex and I'm arriving at my train station now so can't complete this! Will try and check back later.

Hope this helps,

Jason