Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Show date not count with set analysis

Hi

I have created a table which shows a column how many DNA appointments there were in the last 7 days and a column which shows how many DNA appintments in the last 12 months.


image1.jpg

Is there any way I can show the date instead of the count of appointments for the last 7 days column.  See script below:

=(

Count({<[attendance description]={'DNA'},[appointment_date]={">=$(=date(today()-7)) <=$(=date(today()-1))"}>} distinct appointment_id))

Any thoughts?

Phil

6 Replies
datanibbler
Champion
Champion

Hi,

you could try replacing that COUNT(DISTINCT) with the ONLY function - that is another aggregation fct., since you need one of those to be able to use set_analysis, of course - but that will only work if there is only one value to display. Otherwise it will return NULL.

Look it up in the help_file.

HTH

Anonymous
Not applicable
Author

I am not sure what you mean as this will not display the date will it?.  I have provided a view of what I want it to look like....

image2.jpg

MK_QSL
MVP
MVP

=Concat({<appointment_date = {">=$(=Date(Today()-7))<=$(=Date(Today()-1))"}>}appointment_date,', ')

or

=CONCAT(Date(ValueLoop(Today()-7,Today(),1)),', ')

If you want to show values for last 7 appointment_date, you can use as below as calculated dimension

=IF(appointment_date <= Date(Today()-1) and appointment_date >= Date(Today()-7), appointment_date)

Don't forget to tick Suppress When Value is Null

Anonymous
Not applicable
Author

So close to the correct solution, but this shows the date of all appointments within the last 7 days, not just the DNA appt's.  I have tried this, but it's still shows all appointments (not just DNAs).

=

if(

[DNA Appts (in last 7 Days)]>0,
Concat({<appointment_date = {
">=$(=Date(Today()-7))<=$(=Date(Today()-1))"}>}appointment_date,', ')
)


Any other thoughts?

Phil

MK_QSL
MVP
MVP

Is there any flag in your script for DNA appointment?

Anonymous
Not applicable
Author

Sooo nearly cracked it.  I have used the below, but there is one row which has 2 dates on it and I can't understand why as there is only 1 DNA appt in the last 7 days for all?

=

if(

[DNA Appts (in last 7 Days)]>0,

(Concat({<[attendance description]={'DNA'},[appointment_date]={">=$(=date(today()-7)) <=$(=date(today()-1))"}>} appointment_date,', '))
)

image4.jpg