topic Re: get min Year with set analysis in QlikView

get min Year with set analysis

Fri, 09 Sep 2016 19:24:53 GMT

Hi everyone,

I'm wrestling with this problem: I've defined a market with set analysis and I want to get the min Year where sum(Amount) > 0 as a variable. Does anyone know how I would do this?

So my set analysis looks like:

sum({<Company=,Location={'$(vLocation)'}>}Amount)

I thought I would do something like this:

FirstSortedValue(Year,aggr(if(sum({<Company=,Location={'$(vLocation)'}>}Amount)>0,1,0),Year))

But for some reason, the above expression returned an incorrect year.

Is this expression correct or are there other ways?

Thanks.

Re: get min Year with set analysis

sunny_talwar — Fri, 09 Sep 2016 20:30:38 GMT

May be this:

Min({<Year = {"=Sum({<Company, Location = {'$(vLocation)'}>} Amount) > 0"}>}Year)

Re: get min Year with set analysis

MindaugasBacius — Fri, 09 Sep 2016 20:32:10 GMT

=sum(
{$<
  Location = {"=sum({1<Location = {$(vLocation)}>}Amount)>0"}
  ,Company =
>}
Amount)

The vLocation variable:

=Concat({$<Location = {'Location1*'}>}Location, ', ')

The result:

The example is attached.

UPDATE: sorry, misunderstood the issue.

Re: get min Year with set analysis

Mon, 12 Sep 2016 13:23:19 GMT

This didn't seem to work, but thank you for replying

Re: get min Year with set analysis

sunny_talwar — Mon, 12 Sep 2016 13:32:21 GMT

Did it give anything? or did you get an error in syntax? Would you be able to provide a sample with expected output?

Re: get min Year with set analysis

Mon, 12 Sep 2016 13:40:08 GMT

I believe I got it to work with the following:

=Min(if(aggr(SUM({<Company, Location = {'$(vLocation)'}>}Amount),Year)>0,Year),Year)

Re: get min Year with set analysis

Mon, 12 Sep 2016 13:41:40 GMT

The text object gave me a ' - ' . I think I have a solution now

Re: get min Year with set analysis

Anonymous — Mon, 12 Sep 2016 14:39:39 GMT

Is it like ..

if(Aggr(sum({<Company=,Location={'$(vLocation)'}>}Amount)>0,Min(year))