Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karensmith
Contributor II
Contributor II

Multiple set analysis conditions not working

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?



1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Thanks Oleg,

Your sample syntax worked great.

avastani
Partner - Creator III
Partner - Creator III

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.

Not applicable

Hi Amir,

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

Kind Regards

Footsie

avastani
Partner - Creator III
Partner - Creator III

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.