Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Value selected week and same week last year

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?

1 Solution

Accepted Solutions
christophebraul
Contributor III

Value selected week and same week last year

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.

7 Replies
christophebraul
Contributor III

Value selected week and same week last year

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)

Not applicable

Re: Value selected week and same week last year

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.

Not applicable

Re: Value selected week and same week last year

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

E.g.:

Customer

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

A

B

C

D

Only(Customer) in this case will return null.

Customer

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

A

A

A

A

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.pdfhttp://community.qlik.com/docs/DOC-1867

Not applicable

Re: Value selected week and same week last year

To make sure you only have selected one year ?

Not applicable

Re: Value selected week and same week last year

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

Not applicable

Re: Value selected week and same week last year

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.

christophebraul
Contributor III

Value selected week and same week last year

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.

Community Browser