topic Re: Sum value for last week in QlikView

Sum value for last week

Mon, 18 Jun 2012 15:16:32 GMT

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!

Re: Sum value for last week

swuehl — Mon, 18 Jun 2012 16:45:59 GMT

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 )

Re: Sum value for last week

Tue, 19 Jun 2012 08:06:39 GMT

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?

Re: Sum value for last week

swuehl — Tue, 19 Jun 2012 12:51:32 GMT

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)

Re: Sum value for last week

Thu, 21 Jun 2012 07:15:53 GMT

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?

Re: Sum value for last week

swuehl — Thu, 21 Jun 2012 09:59:00 GMT

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

Re: Sum value for last week

Tue, 03 Jul 2012 09:24:30 GMT

Hi Stefan, thanks for the reply.

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

Re: Sum value for last week

swuehl — Tue, 03 Jul 2012 09:56:10 GMT

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

Re: Sum value for last week

Tue, 03 Jul 2012 11:18:19 GMT

Thank you for helpful reply.