Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

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
Master III
Master III

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

View solution in original post

21 Replies
pathiqvd
Creator III
Creator III

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
Master III
Master III

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
Master
Master
Author

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
Master
Master
Author

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
Creator III
Creator III

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
Master
Master
Author

sunday to sat is working fine .

but Monday to sunday is not working

pathiqvd
Creator III
Creator III

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
Master
Master
Author

for me its not working NOTDATE.PNG

pathiqvd
Creator III
Creator III

Hi,

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

Regards,