Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philmywallet
New Contributor III

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

Tags (2)
6 Replies
datanibbler
Esteemed Contributor

Re: Show date not count with set analysis

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

philmywallet
New Contributor III

Re: Show date not count with set analysis

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

MVP
MVP

Re: Show date not count with set analysis

=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

philmywallet
New Contributor III

Re: Show date not count with set analysis

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

MVP
MVP

Re: Show date not count with set analysis

Is there any flag in your script for DNA appointment?

philmywallet
New Contributor III

Re: Show date not count with set analysis

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


Community Browser