
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
IF AND Expression
Hello,
I have some criteria which consists of the following
G/L Number
10040,10041,10045,10050,10051,10055,10060,10061,10065,10100,10101,10105
Job No.
Industry Code
The idea is that:
IF G/L Number = 10040,10041,10045
AND Job No. -='' " (does not equal)
AND Industry Code =" "
SUM(Amount)
Any Ideas on how I can tackle this would be highly appreciated
Thank you in advance for your time
Mark
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this
if((GLnum=10040 or GLnum=10041 or GLnum=10045) and Jobno<>'' and Industrycode='',Sum(Amount),0)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As asked, the above looks correct, though you can avoid the ORs by using a match():
if(match(GLnum,10040,10041,10045) and "Job No."<>' ' and "Industry Code"=' ',sum(Amount))
However, I suspect that what you really want to do is sum up the Amounts of all records where those conditions are true. For that, you do the if() inside of the sum():
sum(if(match(GLnum,10040,10041,10045) and "Job No."<>' ' and "Industry Code"=' ',Amount))
And if you want it to perform well, you use set analysis instead:
sum({<GLnum*={10040,10041,10045},"Job No."-={' '},"Industry Code"*={' '}>} Amount)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This will do:
=Sum({<[G/L Number]={10040,10041,10045},[Job No.]-={'',NULL},[Industry Code]={'',NULL}>} Amount)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
sum(if(match(GLnum,10040,10041,10045) and Isnull(JobNo.) and Isnull(IndustryCode),Amount))
Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello everyone!,
Thank you for your answers. However, syntactically they seem ok, I am not getting any results. I believe this may be because of the tables being dependant of each other. (i'm not 100% sure how Qlikview works) I've attached an image below
so as you can see. IF the there is a Job Number it will look for a customer number. IF there is not a customer number the industry code will obviously be blank (as it cannot find the link). However (please correct me if im wrong), Qlikview will not do this check because there is no link between customer and industry.
I made the assumption that it see's it as blank regardless.
Thanks again
Mark

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So by seeing your cloud you should join the jobcode from the GL table with Jobcode in Job table
even if the column is null so by default nulls will not be joined in qlikview so using the variable
NullAsValue JobNo; Will connect the tables even if the fields have null value by this way
u can see the data

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi John,
I want to learn what does this expression mean
sum({<GLnum*={10040,10041,10045} ....)
I wish to know the significance of the asterisk * used. Wouldn't this expression work without the asterisk *.
Thanks,
Bikash Debnath


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The *= specifies intersection, or as I think of it, it restricts my selections to no more than the indicated values. It's the same behavior as a sum(if()). I can still select just 10040, and I'll then only see 10040. If I select 12345, the chart will not display data, which in some cases may help prevent confusion about what the user is looking at. I typically use the *= as I feel it is a more natural, more normal behavior than the =, which is a pure, static override.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you John,
You really explain well. Love it!
I understood the idea.
with best regards,
Bikash Debnath

- « Previous Replies
-
- 1
- 2
- Next Replies »