Sum value for last week

[#1]

Hi all!

I'm trying to do a expression that´s summarize the amount/value for the hole last week. I,ve a date field with the format YYYYMMDD and the field that a summurizing is integer.

Any one that have a good solution?

Thanks in advance!

[swuehl]

You could use a set expression to limit the values to summarize to a specified date range, like

=sum(

{<DATEFIELD= {">=$(=Date(weekstart(today()-7),'YYYYMMDD))<$(=Date(weekstart(today()),'YYYYMMDD' ))"}>}

VALUEFIELD )

[#3]

Thanks a lot for your reply. So this should give me the hole last week? Should it be weekstart and weekstart or should it be weekend instead?

And another question, do you have a suggestion for a set expression to sum yesterday value?

[swuehl]

Please note that I used "smaller than" today's weekstart in above expression, but you surely can use "smaller equal than" last week's (today()-7) weekend ,too.

To summarize the value for yesterday, I believe it could look like

=sum(

{<DATEFIELD = {"$(=Date(today()-1,'YYYYMMDD'))"} >}

VALUEFIELD)

[#5]

Regarding last week, do you mean something like this:

=sum({<DateField={"<=$(=Date(Lastweek(Today()-7),'YYYYMMDD'))>=$(=Date(Weekend(Today()),'YYYYMMDD'))"}>} ValueField)

Regarding last day it´s really doesn´t work with:

=sum({<DateField={"$(=Date(today()-1,'YYYYMMDD'))"}>} ValueField)

Any suggestions?

[swuehl · Accepted solution]

No, lastweek is not a QV function. I meant to use the functions as stated above (it seems I missed a single quote in the date format of last weeks sum).

See attached a sample file.

Regards,

Stefan

[#7]

Hi Stefan, thanks for the reply.

Do you also have an solution for WTD (week-to-date)?

[swuehl]

Maybe like

=sum(

{<Date= {">=$(=Date(weekstart(today()),'YYYYMMDD'))<=$(=Date(today(),'YYYYMMDD'))"}>}

Value )

Please check out the date and time functions in QV, there is a whole bunch of different functions.

The set expression looks always quite similar, a field modifier with a search expression that consists of ">=LOWERBOUNDDATE<=HIGHERBOUNDDATE"

LOWERBOUNDDATE and HIGHERBOUNDDATE are created using QV date and time functions (maybe using today() or a date field as input), and formatted appropriately if needed.

Regards,

Stefan

[#9]

Thank you for helpful reply.