topic Re: Using set analysis in an expression in QlikView

Using set analysis in an expression

Ryan — Wed, 17 Apr 2019 21:48:35 GMT

I have run into an issue using set analysis. I am trying to roll-up previous quarters minus 4 from the current quarter into 1 column, while the horizon remains filled with their projected quantity’s.

The expression I am using is as follows: Sum({1<YYYYQ={'>=$(Qtr_Minus_4_Qtr_YYYYQ)<=$(Current_QTR_YYYYQ)'}>}COE_QTY)

The result is not adding COE Cumulative Q1 to Q2 as expected.For the below example I would like to have 20192 and Previous.

Re: Using set analysis in an expression

Vegar — Thu, 18 Apr 2019 05:33:57 GMT

If I understand you correctly then this might be what you are looking for:

RangeSum(Above(
Sum({1<YYYYQ={'>=$(Qtr_Minus_4_Qtr_YYYYQ)<=$(Current_QTR_YYYYQ)'}>}COE_QTY)
, 0, RowNo()))

Re: Using set analysis in an expression

Ryan — Thu, 18 Apr 2019 22:15:07 GMT

Thank you for reaching out with a response. I adjusted your expression as follows

if(YYYYQ = Current_QTR_YYYYQ,
RangeSum(Before(Sum({1<YYYYQ={'>=$(Qtr_Minus_4_Qtr_YYYYQ)<=$(Current_QTR_YYYYQ)'}>}COE_QTY), 0, RowNo())),
sum(COE_QTY))

The expression works, however only on the ones following the second row as shown below. Do you know how I could adjust the expression to include the first row?

Thanks