Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for
Did you mean:
Creator II

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

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
QTYWEIGHTTRAN_DATEWHS
a1100011/1/2011A
a1-100011/1/2011A
b1100011/2/2011A
c1100011/3/2011A
c2100011/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!

2 Replies
Creator II
Author

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!)

Luminary Alumni

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

Community Browser