Qlik Community

Qlik Sense App Development

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

laurischarf
Contributor II

Date comparison in set analysis

I am having trouble writing set analysis to find events in a table based on related events in another table.

I have two tables that are linked on a person identifier (Recipient_Unique_ID):

Table 1 contains MedClaimDate, Recipient_Unique_ID, MedRowID* (among other fields)

Table 2 contains PharmClaimDate and Recipient_Unique_ID, PharmRowID* (among other fields)

*both made with RowNo() during load

I have a straight table that should show data from Table 1 if PharmClaimDate is equal to MedClaimDate or MedClaimDate+5 days.


I have tried these variations just to see if I'm on the right track:


This returns all rows, ignoring the set analysis:

Sum({<[MedRowID]={"$(=PharmClaimDate & '>=' & MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

This returns 0 rows:

Sum({<[MedRowID]={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

This returns 0 rows:

Sum({<Recipient_Unique_ID={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])


Thanks for any help.

1 Solution

Accepted Solutions

Re: Date comparison in set analysis

How about using an if statement

Sum(If(PharmClaimDate >= MedClaimDate, [MedicalClaims.Paid_Amount]))

15 Replies

Re: Date comparison in set analysis

How about using an if statement

Sum(If(PharmClaimDate >= MedClaimDate, [MedicalClaims.Paid_Amount]))

Digvijay_Singh
Honored Contributor III

Re: Date comparison in set analysis

May be something like this -


Sum({<PharmClaimDate={"=PharmClaimDate=MedClaimDate"}>} [MedicalClaims.Paid_Amount])

laurischarf
Contributor II

Re: Date comparison in set analysis

Thanks for the idea. That logic is correct, but it doesn't hide the rows that don't meet it. I wanted to use set analysis in order to hide all rows that don't qualify.

Re: Date comparison in set analysis

Really? Can you share a sample where this isn't working

laurischarf
Contributor II

Re: Date comparison in set analysis

To clarify, when I say "not working" I mean the rows aren't being hidden (which is actually working correctly; I wouldn't expect an IF statement to cause rows to be hidden). The 'test' column here is basically the same:


=max(distinct If(PharmClaimDate >= MedClaimDate and PharmClaimDate < MedClaimDate + 6,  MedClaimDate))

Capture.PNG

The result is indeed correct. The selected patient has a prescription within 5 days of 5/18/2015.

I would like the table to show only that row.

Re: Date comparison in set analysis

I think all you need to do is uncheck 'Show zero values' under Add-Ons -> Data Handling

laurischarf
Contributor II

Re: Date comparison in set analysis

BEST TRICK OF THE MONTH!!!

You learn something new every day (on this forum)!

I foresee having to add a few more conditions, with nesting, to this IF statement, so I'm worried about performance. Theoretically set analysis may be better, so I'm interested in making it work (if only for the sake of learning).

Any thoughts as to why my above syntax might not work?

Re: Date comparison in set analysis

If you can join the two tables into one, then the set analysis can work for you.... is that an option?

laurischarf
Contributor II

Re: Date comparison in set analysis

Hmmmmm.... I'll have to think about that. The two tables have only the Patient in common, and each has ~10 million rows, so joining would create a table with many tens or hundreds of millions of rows. I know Qlik does some interesting stuff with storage, so maybe it's not so undesirable.

But the bottom line is the two fields must be in the same table to do a comparison in set analysis?

Many thanks for explaining.

Community Browser