This content has been marked as final. Show 2 replies
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
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,
Thanks for any comments!