Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

soniasweety
Contributor III

Lastweek

Hi all,

I have a data sample attached.

I want to calculate  the  current week count of value and last week count of value.

I have a date field . 

here two conditions : 1  if week starts from Monday to sunday how can I calculate the count   each item wise?

2. if week starts from sunday to Saturday how to caluculate In script?  

Can anyone give some idea on this?

Thanks

Sony

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Lastweek

Script

LOAD *,Week(WeekStart(Date)) as Week1,Week(WeekStart(Date,0,6)) as Week2;
LOAD Number,
Item,
Date,
value
FROM

(ooxml, embedded labels, table is
Sheet1);

Text Objects

='Current Week = '&Max(Week1)&Chr(10)&'Nr Item = '&Count(DISTINCT Item)

='Last Week = '&Max({<Week1={'<$(=Max(Week1))'}>} Week1)&Chr(10)&'Nr Item = '&Count({<Week1={'<$(=Max(Week1))'}>} DISTINCT Item)

Week beginning on Sunday : Replace Week1 with Week2

21 Replies
pathiqvd
Contributor III

Re: Lastweek

Hi ,

Try this,

For Sunday to Sat:-

Current week:-  =count({<Date2={">=$(=WeekStart(max(Date2)))<=$(=max(Date2))"}>}Number)

Prev Week:- =count({<Date2={">=$(=WeekStart(max(Date2)-7))<=$(=WeekEnd(max(Date2)-7))"}>}Number)

For Monday to Sunday:-

Current Week;-

=count({<Date2={">=$(=date(WeekStart(max(Date2))+1,'DD-MM-YYYY'))<=$(=max(Date2))"}>}Number)

Prev Week:-

=count({<Date2={">=$(=date(WeekStart(max(Date2)-7)+1,'DD-MM-YYYY'))<=$(=date(WeekEnd(max(Date2)-7)+1,'DD-MM-YYYY'))"}>}Number)

Regards,

antoniotiman
Honored Contributor III

Re: Lastweek

Hi Sony,

see WeekStart Function

WeekStart(date [, shift = 0 [,weekoffset = 0]])

In  Script

LOAD *,Week(WeekStart(Date)) as Week1,Week(WeekStart(Date,0,6)) as Week2;

Regards,

Antonio

soniasweety
Contributor III

Re: Lastweek

Hi,

thank for reply its not working

as per

For Monday to Sunday:-

Current Week;-   this criteria  it show   ESAA count is 2,  MI -1 BB -1   this should show but  it is showing  each one 6,6,6week1.PNGweek2.PNG

soniasweety
Contributor III

Re: Lastweek

thanks Antony

but same week1 and week2 giving same result ..  and how can use that in expression to get current week count and previous?

pathiqvd
Contributor III

Re: Lastweek

Hi,

Mt side Working,

please check your date field format and Change your date field format

date(Date,'DD-MM-YYYY') as Date

In script and check

isuue1.JPG

Regards,

soniasweety
Contributor III

Re: Lastweek

sunday to sat is working fine .

but Monday to sunday is not working

pathiqvd
Contributor III

Re: Lastweek

Hi,

Check this exp with yours

=count({<Date2={">=$(=date(WeekStart(max(Date2))+1,'DD-MM-YYYY'))<=$(=max(Date2))"}>}Number)

My side working fine

Regards,

soniasweety
Contributor III

Re: Lastweek

for me its not working NOTDATE.PNG

pathiqvd
Contributor III

Re: Lastweek

Hi,

Check your date field format i MM/DD/YYYY so use this format inside exp.

Regards,

Community Browser