topic Re: Show date not count with set analysis in QlikView

Show date not count with set analysis

Anonymous — Wed, 25 Mar 2015 09:54:47 GMT

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

Re: Show date not count with set analysis

datanibbler — Wed, 25 Mar 2015 10:16:31 GMT

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

Re: Show date not count with set analysis

Anonymous — Wed, 25 Mar 2015 10:23:57 GMT

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

Re: Show date not count with set analysis

MK_QSL — Wed, 25 Mar 2015 10:28:22 GMT

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

=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

Re: Show date not count with set analysis

Anonymous — Wed, 25 Mar 2015 10:51:26 GMT

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

Re: Show date not count with set analysis

MK_QSL — Wed, 25 Mar 2015 11:01:41 GMT

Is there any flag in your script for DNA appointment?

Re: Show date not count with set analysis

Anonymous — Wed, 25 Mar 2015 11:07:16 GMT

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