topic SET Analysis AND Syntax in QlikView

SET Analysis AND Syntax

Mon, 22 Feb 2016 19:50:05 GMT

Hi All,

I am trying to do a multiple conditions SET Analysis. I am using the plus sign + as an AND syntax. The table is showing an hyphen instead of the Expression Error depicting I'm on the right track but just can't get the formula to click.

Requirements for SET Analysis:

1) Line Status = Active

Last Billed = Empty

2) Line Status = On Hold

Reason = Implementation Delay - Not Ready,

Implementation Delay - Other,

Implementation Delay - POS Delayed

Please help....

Thanks in advance,

='Backlog MRR ' & money(sum({<[Line Status] = {'Active'},[Last Billed] = {"-"}>}[MRR/12]))

+ money(sum({<[Line Status] = {'On Hold'},Reason = {'Implementation Delay - Not Ready','Implementation Delay - Other','Implementation Delay - POS Delayed'}>}[MRR/12]))

Re: SET Analysis AND Syntax

swuehl — Mon, 22 Feb 2016 19:59:05 GMT

Try

='Backlog MRR ' &

(

money(sum({<[Line Status] = {'Active'},[Last Billed] = {"-"}>}[MRR/12]))

+ money(sum({<[Line Status] = {'On Hold'},Reason = {'Implementation Delay - Not Ready','Implementation Delay - Other','Implementation Delay - POS Delayed'}>}[MRR/12]))

)

i.e. first perform the addition of the two aggregates, then concatenate to the text

Re: SET Analysis AND Syntax

sunny_talwar — Mon, 22 Feb 2016 20:06:43 GMT

Not 100% sure, but may be this:

='Backlog MRR ' & Money(Sum({<[Line Status] = {'Active'},[Last Billed] = {"-"}> + <[Line Status] = {'On Hold'},Reason = {'Implementation Delay - Not Ready','Implementation Delay - Other','Implementation Delay - POS Delayed'}>}[MRR/12]))

Re: SET Analysis AND Syntax

Mon, 22 Feb 2016 20:25:57 GMT

You guys are awesome, I love you guys. Every time I'm stuck you guys help me out.

Thanks a BUNCH.