17 Replies Latest reply: Nov 30, 2013 8:37 AM by Thomas Jensen

# Sum of value by date range (last week & this week)

Hi All,

I'm new to QV and i want to learn more regarding the date formulas/script etc.. Can someone help me with my QV date formula?

I have a list of submitted requests and i want to break the total value per "last week" and "this week"

for example:

Sum of Total by last week "from 17-Nov-13 to 23-Nov- 13"

Sum of Total by this week "from 24-Nov-13 to 30-Nov-13"

So for every last week and this week per month it should be calculated automatically

I attached the sample of my excel list and my QV test..

Thanks

• ###### Re: Sum of value by date range (last week & this week)

There is two things you could do:

Load a master calendar, where you have CY, and LW - Or you could make this by doing date ranges.

So try this:

This week:

sum({<DATEFIELD={">=\$(=Date(Today()) <= \$(=Date(Today()-7)"}>} Amount)

Last Week:

sum({<DATEFIELD={">=\$(=Date(Today()-8) <= \$(=Date(Today()-14)"}>} Amount)

• ###### Re: Sum of value by date range (last week & this week)

Hi Thomas,

I have a master calendar and tried your codes but its not working...  can you please check the attached QV file ive created.

Thanks

• ###### Re: Re: Sum of value by date range (last week & this week)

Hi,

try this:

• ###### Re: Sum of value by date range (last week & this week)

Hi Thomas,

Thanks and its working.. but I'm trying to change the date range, it should be always from Sunday to Saturday

is it possible to do like this:

Sum of Total by last week "from 17-Nov-13 to 23-Nov- 13" the total amount is   \$69,588.12

Sum of Total by this week "from 24-Nov-13 to 30-Nov-13" the total amount is    \$532,940.22

So for the next month ("last week" and "this week") it will calculate by:

Sum of Total by last week "from 24-Nov-13 to 30-Nov-13"

Sum of Total by this week "from 01-Dec-2013 to 07-Dec-2013"

Thanks

• ###### Re: Sum of value by date range (last week & this week)

Ahhh okay, Yes it is - I will get back to you later on today - have to go out. Ill post the solution when i get back - if nobody else has

• ###### Re: Sum of value by date range (last week & this week)

Sure.. no problem.. thanks

• ###### Re: Sum of value by date range (last week & this week)

Just a questions:

Lets say we have the 5 december

Should This week then be from 1st dec to 5 december and when you get to the 6th deecmber it should be from 1st-6th december until the 7th december? and so forth

and last week should be from 24thnov to 30th nov?

• ###### Re: Sum of value by date range (last week & this week)

Yes, the sum range should be from Sunday to Saturday of last week and this week

• ###### Re: Sum of value by date range (last week & this week)

Im back

Can you load these two scripts into your mastercalendar and then reattach the sample?

InWeek(TempDate, \$(vToday), -1, -1) * -1 as LWSundayToSaturday //Last week from sunday to saturday

InWeek(TempDate, \$(vToday), 0, -1) * -1 as TWSundayToSaturday //This week sunday to saturday

You should of cause change TempDate with your Datefield

• ###### Re: Re: Sum of value by date range (last week & this week)

Hi,

Thanks

• ###### Re: Sum of value by date range (last week & this week)

Hmmm- I get no numbers when selecting CMTD,CMPTD, and so forth.

Could you try this:

LET vMinDate = Num(MakeDate(2013,1,1));   // Alternativen, falls Datum fix definiert werden soll

LET vMaxDate = Num(Floor(yearend(Today())));  //This one is to update the date to the current date.

Let vToday = num(Today());

and make your dateformat like this:

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

• ###### Re: Re: Sum of value by date range (last week & this week)

here you go..

ahmm.. if still didn't work can we use your script without calendar right?

• ###### Re: Re: Re: Sum of value by date range (last week & this week)

Like this?

• ###### Re: Re: Re: Sum of value by date range (last week & this week)

It should be.

Look at this example:

I have setup two listboxes.

1: Showing this weeks dates (from 24-30)

2: Status listbox where i select Released

3: Next to that there is a chart which shows the sum of Total Amount.. It gives the same.

• ###### Re: Re: Sum of value by date range (last week & this week)

Thanks a lot Thomas! for helping me