Skip to main content
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 
Search instead for 
Did you mean: 
mellerbeck
Creator II
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
mellerbeck
Creator II
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!)

Jason_Michaelides
Luminary Alumni
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