Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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.