Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
sneelakantan
Partner
Partner

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?

Thank You in Advance.

 

Labels (2)
1 Solution

Accepted Solutions
sneelakantan
Partner
Partner
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.

View solution in original post

4 Replies
rubenmarin

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), '#,###.')

sneelakantan
Partner
Partner
Author

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

Brett_Bleess
Support (Former)
Support (Former)

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.
sneelakantan
Partner
Partner
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.

View solution in original post