21 Replies Latest reply: Aug 8, 2017 8:33 AM by Magdalena Tudose

# 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

• ###### 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,

• ###### 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,6

• ###### Re: Lastweek

Hi,

Mt side Working,

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

In script and check

Regards,

• ###### Re: Lastweek

sunday to sat is working fine .

but Monday to sunday is not working

• ###### 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,

• ###### Re: Lastweek

for me its not working

• ###### Re: Lastweek

Hi,

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

Regards,

• ###### Re: Lastweek

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

 23-Jul-17
• ###### Re: Lastweek

cahnge in script which format do you want like

date(Date,'format') as Date

Regards,

• ###### Re: Lastweek

ok thank you I will check and update you the same

• ###### 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

• ###### 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?

• ###### Re: Lastweek

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

What is Your expected result ?

• ###### Re: Lastweek

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

• ###### Re: Lastweek

I want to see the  text object /bar chart

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

• ###### Re: Lastweek

Script

LOAD *,Week(WeekStart(Date)) as Week1,Week(WeekStart(Date,0,6)) as Week2;
Item,
Date,
value
FROM
[C:\Users\mancini\Documents\QV_Test\lastweek.xlsx]
(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

• ###### Re: Lastweek

thanks Antony,

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

• ###### Re: Lastweek

At same mode as above expressions.

Replace Week1 with Your Week in Expressions

• ###### Re: Lastweek

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

• ###### Re: Lastweek

You need to select WeekNumber 28

or

Count({<WeekNumber={'\$(=Max(WeekNumber))'}>} DISTINCT ID)

• ###### Re: Lastweek

Hi,

Can someone to atash me an simple example (for Qlik Sense) who resume this conversation?

I don't know how i can applique your advices to show the value from last week.

Thank you,

Magdalena