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

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

mellerbeck — Wed, 14 Sep 2011 17:48:27 GMT

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 — Thu, 15 Sep 2011 17:25:29 GMT

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 — Thu, 15 Sep 2011 19:04:12 GMT

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