topic Re: Set analysis with sequence numbers (go back twelve months) in QlikView

Set analysis with sequence numbers (go back twelve months)

mellerbeck — Tue, 29 Apr 2014 18:25:11 GMT

So I have some months that have sequence numbers. Let's say I select a couple sequence numbers 25,26,27

sum({$<PeriodSeqNum>} product_weight) gives me the sum of the product_weight for that sequence. What I would like is an expression that would give me the sum of 13,14,15 (12 minus those selections)

I tried various iterations of

sum({$<PeriodSeqNum = {$(=(PeriodSeqNum)-12)}>} product_weight)

but haven't hit on the answer!

Thanks!

Re: Set analysis with sequence numbers (go back twelve months)

MK_QSL — Tue, 29 Apr 2014 19:08:00 GMT

Have your tried?

sum({$<PeriodSeqNum = {'$(=(PeriodSeqNum)-12)'}>} product_weight)

Sorry , use below. This should work...

SUM({<PeriodSeqNum = {'>=$(=Min(PeriodSeqNum)-12) <= $(=Max(PeriodSeqNum)-12) '}>}product_weight)

Re: Set analysis with sequence numbers (go back twelve months)

Tue, 29 Apr 2014 20:51:19 GMT

Michael,

you may use expression like this:

sum({<PeriodSeqNum={$(=concat(aggr(PeriodSeqNum-12,PeriodSeqNum),','))}>}product_weight)

let me know if it works

regards

Darek

p.s

remamber, that if user selects in another field than sequence nbr (for example month), you should add to your set analysis part like this month=

Re: Set analysis with sequence numbers (go back twelve months)

Tue, 29 Apr 2014 21:02:04 GMT

Michael,

you may also consider solution as I proposed in this thread:

Can this be done with set analysis?

regards

Darek