Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!)
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