Multiple set analysis conditions not working

[karensmith]

The first half of my set analysis statements works great, but trying to combine another standalone functioning set analysis statement I can not get a result

Background explaination: This statement is based on distributor selection, It sums all values with the exception of the value selected.

sum({<Distributor = {*}-{$(=chr(39) & Distributor & chr(39))}>}

statement b:

works in another chart. It calculates previous month based on month sequence calculation. In this chart I have three 3 fields: -1, -2, -3. .

{<FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)}>} POS_Qty

sum

({<Distributor = {*}-{$(=chr(39) & Distributor & chr(39))}>} + {<FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)}>} POS_Qty)

Goal: I would to sum POS Qty for all other distributors and get the sum of the previous 3 months. I don't mind if I have to do this in three expression and then sum, but I was hoping to accomplish this in one expression.

Any recommendations or suggestions?

[Oleg_Troyansky · Accepted solution]

Karen,

I see two issues here - one is pure syntax, and another is logical.

1. From the syntax standpoint, I don't think you can add Set Analysis expression the way you did... In other words, your Set Analysis expression should have one set of outer curved brackets. For example, expression {1-$} is valid, while {1} - {$} is not. Do you see the distinction?

2. From the logical standpoint, even if your expression worked, it would've returned summarized sales for all the excluded customers OR if the month is within the last 3 months - your logical "+" signified a union between the two sets. Are you sure this is what you are truly after? If I understand your business problems, you'd like to know the sales for all excluded customers, that happened within the last 3 months. In this case, you don't need a union, you rather need to list your set modifiers one after another, separated by coma.

sum({< Distributor = {*}-{$(=chr(39) & Distributor & chr(39))} , FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)} >} Sales)

cheers,

Oleg

[karensmith]

Oleg,

Okay I understand point #1- one set of curly brackets. Thanks for the correction.

Point #2- No I truly did not want an union statment. I was trying to add three months based on excluded distributors

And, yes your understanding is correct. I thougt I tried your recommend syntax,but with no success. I will try syntax again.

Thank you so much.

[karensmith]

Thanks Oleg,

Your sample syntax worked great.

[avastani]

On a similar note, what is wrong with this expression where I am trying to get the difference between the Sales this Month Year and the last year same time,

Sum({$<[Fiscal Period]={$(=Only(Fiscal Period))}>} Sales) - Sum({$<[Fiscal Period]={$(=Only(Fiscal Period)-12)}>} Sales)

somehow, qlikview is not interpreting this properly. the first part works but the second one doesn't.

[#6]

Hi Amir,

You are missing square brackets [] around Fiscal Period in the two Only(Fiscal Period) parts of your expression.

Kind Regards

Footsie

[avastani]

I put that in and it still didn't do any good. For some reason my version 8.50 is not accepting Only() in set analysis.