topic Multiple set analysis conditions not working in QlikView

Multiple set analysis conditions not working

karensmith — Thu, 03 Sep 2009 22:45:17 GMT

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?

Multiple set analysis conditions not working

Oleg_Troyansky — Thu, 03 Sep 2009 23:04:03 GMT

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

Multiple set analysis conditions not working

karensmith — Thu, 03 Sep 2009 23:32:48 GMT

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.

Multiple set analysis conditions not working

karensmith — Fri, 04 Sep 2009 01:26:49 GMT

Thanks Oleg,

Your sample syntax worked great.

Multiple set analysis conditions not working

avastani — Wed, 23 Sep 2009 23:28:55 GMT

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.

Multiple set analysis conditions not working

Thu, 24 Sep 2009 01:36:37 GMT

Hi Amir,

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

Kind Regards

Footsie

Multiple set analysis conditions not working

avastani — Thu, 24 Sep 2009 01:41:55 GMT

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.