topic Re: Value selected week and same week last year in QlikView

Value selected week and same week last year

Tue, 29 May 2012 09:40:33 GMT

Hey,

A quick question, what is the best way to get this:

User select 201230(2012 week 30) you show the sum from all sold products in that week, and from the same week last year.

They insert it now, by hand, in Excel next to each other in a table, but it seems to me that's difficult to do it in a table in qlikview?

any ideas?

Value selected week and same week last year

christophebrault — Tue, 29 May 2012 11:53:35 GMT

Hi,

It's not so hard. If you have a calendar with fields : Year and Week

With this expression you get the current week but year - 1 :

=SUM({$<Year={$(=Only(Year)-1)}>} Sales)

Re: Value selected week and same week last year

Thu, 31 May 2012 09:01:52 GMT

hi,

Thanks it worked, but what does only() mean?

and what if i want to show, if the user choose 17, the sum till week 17 that year?

With the help in f1 about set analyse i can't get it to work.

Re: Value selected week and same week last year

Thu, 31 May 2012 09:57:44 GMT

The only functions returns a value if there is only one distinct value.

E.g.:

Customer

-------------

Only(Customer) in this case will return null.

Customer

-------------

Only(Customer) in this case will return the value 'A'.

A nice document about set analysis that explains it quite nice: /servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Presentation.pdf http://community.qlik.com/docs/DOC-1867

Re: Value selected week and same week last year

Thu, 31 May 2012 09:59:48 GMT

To make sure you only have selected one year ?

Re: Value selected week and same week last year

Thu, 31 May 2012 10:10:48 GMT

The other way around: Only returns the year if you have only selected one distinct year.

Re: Value selected week and same week last year

Thu, 31 May 2012 10:14:26 GMT

Thanks and could you tell me how to do this?

if i want to show, if the user choose 17, the sum till week 17 that year?

With the help in f1 about set analyse i can't get it to work.

Value selected week and same week last year

christophebrault — Thu, 31 May 2012 10:35:37 GMT

hi,

You can use only() if the user has selected a year, or Max() if more than one year is selected. With Max(), the set analysis uses automaticaly the highter year.

To cumulate, use this set analysis :

sum({$<Week={"<=$(=Max(Week))"},Year={$(=Max(Year))}=>}Sales)

You have the sum from week 1 to 13 for the selected year or the highter year.