Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor II

Set Analysis with multiple conditions on same field

Hi All,

I have a expression that counts the number of distinct stores based on the following conditions:

if sum of both shipped units is greater than 25 and sls_dlrs is greater than 0 for the last 6 months

OR if sum of sales dlrs over the last 6 months is greater than 100K

Below is the set analysis expression I have used. The expressions however doesnt seem to work for the second condition ie sum of sls dlrs is greater than 100K

num(count({<\$(\$1), StoreKey={'=(sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}[Shipped Units])>25 and sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}SLS_DLRS)>0)+(sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}[SLS_DLRS])>100000)'}>} distinct StoreKey), '#,###.')

Is there something I am missing in the expression?

Labels (2)

• 'set expressions'

1 Solution

Accepted Solutions
Partner - Contributor II
Author

Thank you for the help, however on deeper investigation I found that the set analysis expression works fine and the issue was more so on the data side.

4 Replies
MVP

Hi, I think the + should be an 'or':

num(count({<\$(\$1), StoreKey={'=(sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}[Shipped Units])>25 and sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}SLS_DLRS)>0) or (sum({1<weekID={">=\$(=max(weekID)-24)<=\$(=max(weekID))"}>}[SLS_DLRS])>100000)'}>} distinct StoreKey), '#,###.')

Partner - Contributor II
Author

I had used 'or' earlier however the second condition doesn't trigger.

Former Employee

It may be most helpful if you can attach a sample QVW on this one I think, otherwise may be difficult for anyone to really help given complexity of things in this case.  The best I can do otherwise is the following, which is a specific Design Blog post on Set Analysis, that does have some additional related links at the bottom:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Here is the base Design Blog URL in case you want to search further yourself, as there may be something better I am missing:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

The only other thing is the Help, you can check that to be sure what you have is working as documented, as if not, then we may have possible defect in which case the best thing would be to open a support case with us, so we can dig into things with you.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Sorry I do not have anything better for you, but again, attaching a sample QVW etc. should likely help get some further guidance too.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Partner - Contributor II
Author

Thank you for the help, however on deeper investigation I found that the set analysis expression works fine and the issue was more so on the data side.

Community Browser