Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
toddbuss
Creator
Creator

Comparing two sets to find missing/added values (with formula?)

I have a dashboard that loads files from consecutive Fridays showing a list of patients.

Relevant fields:

ReportDate     Patient ID

What I want to do is count how many patients are on the latest report date, but not on the report date 7 days prior.  This will show me an estimate of the number of admissions during the week.

I also want to show how many were on the report 7 days prior, but not on the last report date to show the number of discharges in the week. 

Will I need to do some kind of interval match in my data load, or is this something I can do in a formula?  All I really need for now is the two values in KPI boxes.

Thanks.  You are all amazing.

1 Solution

Accepted Solutions
sunny_talwar

May be this

=Count(DISTINCT {<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 6) & '<=' & Date(Max(ReportDate)))"},

[DOCNUM #] = e({<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 13) & '<=' & Date(Max(ReportDate)-6))"}>})>} [DOCNUM #])

and this

=Count(DISTINCT {<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 13) & '<=' & Date(Max(ReportDate)-6))"},

[DOCNUM #] = e({<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 6) & '<=' & Date(Max(ReportDate)))"}>})>} [DOCNUM #])

View solution in original post

9 Replies
sunny_talwar

Would you be able to share a sample Todd?

toddbuss
Creator
Creator
Author

I couldn't figure out how to upload a file within the chat, so I re-posted here.   I welcome any correction to my poor forum etiquette. 

https://community.qlik.com/docs/DOC-18629

sunny_talwar

You can look here for how to attach sample

Uploading a Sample

Anonymous
Not applicable

I think this will work for the patients who are in the last report but not in the previous:

count({<ReportDate={"$(=date(max({1}ReportDate)))"}> - <ReportDate={"$(=date(max({1}ReportDate,2)))"}>} distinct [Patient ID])

To inverse the logic, move that ,2 to the first part

sunny_talwar

May be this

=Count(DISTINCT {<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 6) & '<=' & Date(Max(ReportDate)))"},

[DOCNUM #] = e({<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 13) & '<=' & Date(Max(ReportDate)-6))"}>})>} [DOCNUM #])

and this

=Count(DISTINCT {<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 13) & '<=' & Date(Max(ReportDate)-6))"},

[DOCNUM #] = e({<ReportDate = {"$(='>=' & Date(Max(ReportDate) - 6) & '<=' & Date(Max(ReportDate)))"}>})>} [DOCNUM #])

toddbuss
Creator
Creator
Author

Thanks so much!!  The formula works on the sample I provided.   Now I'm trying to understand how it works.  It looks like the formulas would also count admissions/discharges for any intermediate (non-friday) reports that might get interlaced into the set. 

My attempt at interpretation:

Run Set analysis limited to the records with a report date within 7 days of the last Friday and prior to the last Friday

Count the resulting [DOCNUM#]........=e?... what does this do?

toddbuss
Creator
Creator
Author

thanks for your reply.  I pasted your formula into my demo sheet and got zeros for results.  I do like the elegance of your method.

sunny_talwar

exclude DOCNUM# from previous week....

toddbuss
Creator
Creator
Author

Aaah.  New trick for me.  Thanks.