Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Set modifier for multiple selections in fields

Hi,

I am new in Qlikview set analysis. I am trying to sum the [PL Item Value] by having the selections both in [PL Item Name]='Revenue' and [Position Date]=max(Year([Position Date])). However, each of the expressions below does not work.

This expression has error,

i).     sum({$<[PL Item Name]={'Revenue'},[Position Date]={=max(year([Position Date]))}>}[PL Item Value])

While this expression returns 0 value,

ii).    sum({$<[PL Item Name]={'Revenue'},[Position Date]={$(=max(year([Position Date])))}>}[PL Item Value])

I am building a bar chart which I would like to express the summation of [PL Item Value] of [PL Item Name]='Revenue', meanwhile it is always returning the [PL Item Value] of [PL Item Name]='Revenue' of the most current Year([Position Date]).

Attached is the data set.

Please help. Thanks.

3 Replies
MVP
MVP

Re: Set modifier for multiple selections in fields

Hi

Your second expression is syntactically correct, but I am not sure that it makes sense. The expression assumesthat Is the field [Position Date] a year value?

I suspect that you need something like this:

sum({$<[PL Item Name]={'Revenue'},[Position Date]={'$(=Date(max([Position Date])))'}>} [PL Item Value])

Or

sum({$<[PL Item Name]={'Revenue'},[Position Date]={'$(=YearStart(max([Position Date])))'}>} [PL Item Value])

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Set modifier for multiple selections in fields

Hi,

Both expressions return 0 value of [PL Item Value], instead of 41858.8, for max([Position Date]).

It does not matter if [Position Date] is a year value or not. As long as the sum([PL Item Value]) is expressed by both the selections of [PL Item Name]='Revenue' and [Position Date]=max([Position Date]).

I have even created and used a variable vSelectedPositionDate (=if(GetSelectedCount([Position Date]), max(Year([Position Date])), Year([Position Date]))), into the expression, but it is still not working.

Am I able to set any expression, if I combine the [PL Item Name] and [Position Date] into a single field [PL Date-Item Name]? The new field will be alphanumeric field type, in theory can not be filtered.

Thanks for your help, Jonathan.

MVP
MVP

Re: Set modifier for multiple selections in fields

Hi

It matters because that changes how you would write the set expression. A year value is a number, not a date.

Please post your model or a sample so that we can look at it in a little more detail.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser