topic Re: Show data from last 7 days except Saturday and Sunday (Set Analysis) in QlikView

Show data from last 7 days except Saturday and Sunday (Set Analysis)

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I'm trying to create one expression where the result can show the current + last 7 days except for Saturday and Sunday.

My current formula is:

Today:

Count({<%Date = {"$(=date(OrderDate,'YYYY-MM-DD'))"}>} Distinct ProductID)

Yesterday:

Count({<%Date = {"$(=date(OrderDate-1,'YYYY-MM-DD'))"}>} Distinct ProductID)

Ideal solution:

One formula.

Thanks in advance.

Re: Show data from last 7 days except Saturday and Sunday (Set Analysis)

rodjager — Tue, 29 May 2018 09:53:00 GMT

Hi M,

There are a few different ways you can do this.

I have attached a sample which uses the weekstart and weekend functions to set date variables and then in set analysis I have picked up the date range but excluded Saturdays and Sundays as per my calendar.

The end result is a single formula which is expressed as:

=count({$<WeekDay-={Sat, Sun}, Date={">=$(vStartDate)<=$(vEndDate)"}>}distinct ProductId)

Hopefully this helps.

Rod

Re: Show data from last 7 days except Saturday and Sunday (Set Analysis)

tresB — Tue, 29 May 2018 09:58:01 GMT

I guess lastworkdate() is what you need. Try something like:

Count({<%Date = {" >=$(=today()) <=$(=date(lastworkdate(today(),7),'YYYY-MM-DD'))"}>} Distinct ProductID)

Re: Show data from last 7 days except Saturday and Sunday (Set Analysis)

qlikviewwizard — Tue, 29 May 2018 10:14:14 GMT

Hi,

Try like this.

count({<Day-={'Sat','Sun'},Date={">=$(=date(max(Date)-6))<=$(=max(Date))"}>}ProductID)

Re: Show data from last 7 days except Saturday and Sunday (Set Analysis)

shruthibk — Tue, 29 May 2018 10:15:31 GMT

Create a weekday(OrderDate) as OrderDateWeekDays field in the backend and use the expression

=count({<OrderDate={">=$(=max(OrderDate)-6) <=$(=max(OrderDate))"},OrderDateWeekDays={'Mon','Tue','Wed','Thu','Fri','Sat'}>} Distinct ProductID)