Set modifier for multiple selections in fields

[#1]

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.

[jonathandienst]

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

[#3]

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.

[jonathandienst]

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