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

[#1]

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

[#2]

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)

[#3]

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

[#4]

Hi,

try this:

[#5]

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

[#6]

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

[#7]

Sure.. no problem.. thanks

[#8]

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?

[#9]

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

[#10]

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