Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Like this?
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)
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
Hi,
try this:
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
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
Sure.. no problem.. thanks
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?
Yes, the sum range should be from Sunday to Saturday of last week and 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