Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Set Analysis - IF on a non aggregation function

Hello experts

First of all, I am kind of new in Qlik Sense, so apologies in advance if this is something pretty obvious.

I am creating an app to analyse invoice data. There are two different date fields in the data model, one of them has many NULL values (Date1) and the other hasn't (Date2).

What I am trying to do is to create a unique field date which takes the value from Date2 field in case it is not NULL, otherwise it should take the date from Date1, which has value in every record.

I am trying to do it directly in the table graph by setting this function as a measure:

IF([Date1]=' ', [Date2],[Date1])

But the result is not as nice as I expected, because it is working just fine for records where Date2 is not NULL, as it gets the Date2 value in those cases. But in the rest of records is not displaying any value at all, so basically what is doing this function is copying Date2 column.

I am trying to fix the function, but all the IF function examples in set analysis that I have been able to find by searching in google so far, are aggregation function related. Therefore I am kind of clueless.

Thanks in advance and regards

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis - IF on a non aggregation function

Maybe try

=IF(Len(Trim([Date1]))=0, [Date2],[Date1])

if you are using this in a chart expression, consider using aggregation functions!

Use Aggregation Functions!

5 Replies
MVP
MVP

Re: Set Analysis - IF on a non aggregation function

Maybe try

=IF(Len(Trim([Date1]))=0, [Date2],[Date1])

if you are using this in a chart expression, consider using aggregation functions!

Use Aggregation Functions!

Highlighted
MVP
MVP

Re: Set Analysis - IF on a non aggregation function

Try this with Alt:

Alt(Date2, Date1)

Alt(Date1, Date2)

Not applicable

Re: Set Analysis - IF on a non aggregation function

Hello swuehl and Sunny T

First of all, lot of thanks for your help and your replies, both of them have lead me into the desired result. I am marking swuehl's as the correct one because he commented first, but the two answers were hugely useful to me.

Thanks again and regards

MVP
MVP

Re: Set Analysis - IF on a non aggregation function

Javier -

AFAIK we don't worry about who gets the correct answer as long as one or both of us is able to help you attain what you wanted.

Best,

Sunny

Not applicable

Re: Set Analysis - IF on a non aggregation function

Both of you have provide me an incredible help, because not only you have solved my problem, but you have also taught me things that I didn't know, like the 'Alt' function and the good practice of using aggregation functions (which also explains why all the IF examples of set analysis that I had been able to find had aggregation functions), this is really great and I am very grateful!

Regards