topic Re: count today - 7 days in QlikView

count today - 7 days

Tue, 14 Jul 2015 13:56:06 GMT

Hi all

I have the following fields:

Date

Activity_id

I also have a table chart with the following expression: count(Activities_id).

I want to count (Activities_id) using set analysis, so that I'll count all the activities which were performed 7 days ago till today.

I'm not sure how I can do that... I'm using the following expression in order to count last week activities:

=count

(DISTINCT

{$<[YearWeek_Num] = {$(=MAX([YearWeek_Num])-1)}>}

[Activities_id]

)

can someone please advise?

thanks!

Re: count today - 7 days

alexandros17 — Tue, 14 Jul 2015 13:58:34 GMT

I should do:

count

(DISTINCT

{$<[YearWeek_Num] = {$(=(year(Today()-7)*100+week(today()-7)))}>}

[Activities_id]

)

Re: count today - 7 days

Anonymous — Tue, 14 Jul 2015 14:00:56 GMT

Assuming that the field names are Date and Activity_id, this will work:

count({<Date={">=date(today()-7)<=date(today()"}>} distinct Activity_id)

Re: count today - 7 days

sgrice — Tue, 14 Jul 2015 15:30:07 GMT

count({<Date={">=date(today()-7)<=date(today())"}>} distinct Activity_id)

U missed a Bracket

Re: count today - 7 days

Anonymous — Tue, 14 Jul 2015 15:31:57 GMT

thanks

Re: count today - 7 days

Wed, 15 Jul 2015 08:21:34 GMT

Hi! thank you for your help.

for some reason the result is '0' although I have activities in the last 7 days

please check the attached example...

thanks

Re: count today - 7 days

nikhilgandhi715 — Wed, 15 Jul 2015 08:54:36 GMT

Hi Michal,

Please try below expression.

count({<Date={">=$(=date(today()-7))<=$(=date(today()))"}>} Activity_id)

Re: count today - 7 days

Anonymous — Wed, 15 Jul 2015 09:18:54 GMT

OK, here is a more reliable way:

=count({<Date={"$(='>='&date(today()-7)&'<='&date(today()))"}>} distinct Activity_id)

Re: count today - 7 days

krishna20 — Wed, 15 Jul 2015 09:29:19 GMT

Hi,

Michal

Try this

=count({<Date={"=date(today()-7)"}>} distinct Activity_id)

Re: count today - 7 days

MayilVahanan — Wed, 15 Jul 2015 09:40:23 GMT

Try like this

count({<Date={">=$(=date(today()-7))<=$(=date(today()))"}>} distinct Activity_id)

Re: count today - 7 days

Wed, 15 Jul 2015 10:51:24 GMT

Thanks!!!

Re: count today - 7 days

Digvijay_Singh — Wed, 15 Jul 2015 10:53:14 GMT

I was trying to expression

=Count({<Date1={">=$(=Date(today()-7))<=$(=Date(today()))"}>} Distinct Activity_ID),

It started working when I corrected my source date using preceding load,

Load Date#(Date,'DD/MM/YYYY') as Date1,

Unfortunately as of now I don't have licensed version so cannot see ur QVW, just check if you have similar problem. Thanks.