Show date not count with set analysis

[Anonymous]

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.

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

[datanibbler]

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]

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....

[MK_QSL]

=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]

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]

Is there any flag in your script for DNA appointment?

[Anonymous]

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,', '))
)