topic Re: Set modifier for multiple selections in fields in QlikView

Set modifier for multiple selections in fields

Sun, 17 Jun 2012 11:59:30 GMT

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.

Re: Set modifier for multiple selections in fields

jonathandienst — Sun, 17 Jun 2012 18:15:06 GMT

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])

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

Hope that helps

Jonathan

Re: Set modifier for multiple selections in fields

Mon, 18 Jun 2012 06:26:38 GMT

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.

Re: Set modifier for multiple selections in fields

jonathandienst — Tue, 19 Jun 2012 07:55:33 GMT

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