topic Re: Set analysis and Today() in QlikView

Set analysis and Today()

Tue, 29 Oct 2013 10:46:31 GMT

Dear all,

I have a question on set analysis icw ‘Today()’. I would like to have a table with the following data:

Relation	Article	Week-2	Week-1	Current week
A	1	#	#	#
B	2	#	#	#
C	2	#	#	#

I was thinking to create this with a set analysis but I cannot seem to get it working. Who knows how to?

Thanks!

Re: Set analysis and Today()

tresB — Tue, 29 Oct 2013 10:48:35 GMT

Could you post a sample?

Re: Re: Set analysis and Today()

Tue, 29 Oct 2013 11:00:37 GMT

Here you yo. Now I have the data for the 26th and the 28th 'fixed' but I want to have it as Today and Today-1 (or Week and week-1)

Re: Set analysis and Today()

Gysbert_Wassenaar — Tue, 29 Oct 2013 11:04:26 GMT

Try:

sum({<Date={'$(=date(today()))'}>}Amount)

sum({<Date={'$(=date(today()-1))'}>}Amount)

sum({<Date={'>=$(=date(today()-6))<=$(=date(today()))'}>}Amount)

sum({<Date={'>=$(=date(today()-13))<=$(=date(today()-7))'}>}Amount)

Re: Set analysis and Today()

Tue, 29 Oct 2013 11:24:03 GMT

Thanks! It works perfectly for the today() and Today-1, but for the week I need the 'rounded' weeks so monday-sunday. Do you have a solution for that too?

Re: Re: Set analysis and Today()

tresB — Tue, 29 Oct 2013 11:32:35 GMT

May be like attached sample?

Re: Set analysis and Today()

Gysbert_Wassenaar — Tue, 29 Oct 2013 11:34:08 GMT

The weekstart and weekend functions should do the trick:

sum({<Date={'>=$(=date(weekstart(today())))<=$(=date(weekend(today())))'}>}Amount)

Re: Set analysis and Today()

Tue, 29 Oct 2013 11:49:17 GMT

Almost there. It works for the current week ( G Wassenaar) but not for the week-1. I get the whole datset as response, not only the week-1.

Any ideas?

Re: Set analysis and Today()

Gysbert_Wassenaar — Tue, 29 Oct 2013 11:58:46 GMT

sum({<Date={'>=$(=date(weekstart(today()-7)))<=$(=date(weekend(today()-7)))'}>}Amount)

Re: Set analysis and Today()

Tue, 29 Oct 2013 12:00:46 GMT

Perfect!