Lastweek

[soniasweety]

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

[soniasweety]

but in original data format is  like below na  how come it is changing ?

23-Jul-17

[pathiqvd]

cahnge in script which format do you want like

date(Date,'format') as Date

Regards,

[soniasweety]

ok thank you I will check and update you the same

[antoniotiman]

LOAD *,Week(WeekStart(Date)) as Week1,Week(WeekStart(Date,0,6)) as Week2;
LOAD number,
Type,
Task,
ElapsedTime,
EstimatedCompletionDate,
Date(Floor(TaskOpenDate)) as Date,
Priority,
TaskState
FROM
[lastweek.xlsx]
(ooxml, embedded labels, table is DAta);

What is Your expected result ?

[soniasweety]

sorry for miss lead

I was trying  with the data  sheet name  is "Sheet1 "

[soniasweety]

I want to see the  text object /bar chart

count of numbers of each item  in current week and last week separate text objects.

[antoniotiman · Accepted solution]

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

[soniasweety]

thanks Antony,

Antonio Mancini

if I have a week column in data how can I use the expression  for  current week and last week?

my column :

week

27

28

[antoniotiman]

At same mode as above expressions.

Replace Week1 with Your Week in Expressions

[soniasweety]

if use that it is not giving the correct already tried.  I have week  28 with 100 records ,and last week 27 have 2 records but it is showing for current week total 102 I need only 100 as out put.

2807297	28
2807296	28
12345	27
12346	27