Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

Like this?

17 Replies
Not applicable

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)

Not applicable

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

Not applicable

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

Hi,

try this:

Not applicable

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

Not applicable

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

Not applicable

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

Sure.. no problem.. thanks

Not applicable

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?

Not applicable

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

Not applicable

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

Community Browser