topic Re: Counting number of occurences of day of week in last 7 days in QlikView

Counting number of occurences of day of week in last 7 days

DutchHans — Sat, 16 Nov 2024 01:37:32 GMT

I have a dataset containing all the login dates of users during the last 364 days (52 x 7). The field name is Start and is a datetime value.

I am using a Bar Chart to display the mean value of logins on a specific day a week. The used dimension is WeekDay(Start).

I have two expressions:
Count({$<DefaultOgId={">0"}>}Start)/52
Count({$<DefaultWnId={">0"}>}Start)/52

This works fine (see attached capture.png), but now I want to add two extra expressions that only show the number of logins in the last 7 days of the dataset. So I can see the most recent counts alongside the mean value on that day during the last year.

I cannot figure out how to make that work!

This does not work: Count({$<DefaultWnId={">0"},Start={">=$(=Today()-6)"}>}Start)

Re: Counting number of occurences of day of week in last 7 days

Taoufiq_Zarra — Sat, 28 Dec 2019 19:50:36 GMT

can you test :

Count({$<DefaultWnId={">0"},Start={">=$(=Date(Today()-6))"}>}Start)

Re: Counting number of occurences of day of week in last 7 days

DutchHans — Sat, 28 Dec 2019 22:19:37 GMT

Hello Taoufiq,

I just tried. There is no error but it results in no values.

There are however login values for the last week in the dataset.

Re: Counting number of occurences of day of week in last 7 days

DutchHans — Sat, 28 Dec 2019 22:41:33 GMT

It seems to work like this:

=Sum({$<DefaultOgId={">0"}>}if(Start>ToDay()-6,1,0))

Re: Counting number of occurences of day of week in last 7 days

Brett_Bleess — Mon, 30 Dec 2019 14:17:03 GMT

It appears you got things sorted, I am going to mark your last post as the solution given you confirmed that worked. I just wanted to pass along some other potentially useful info. Here is a specific Design Blog link related to dates in set analysis that may be useful, and below that is the base URL to the Design Blog area in case you want to search further now or in the future. There are hundreds of how-to related posts in this area from our experts, so should be helpful when looking for ideas on how to approach different topics.

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett