get min Year with set analysis

[#1]

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.

[sunny_talwar]

May be this:

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

[MindaugasBacius]

=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.

[#4]

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

[sunny_talwar]

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

[#6 · Accepted solution]

I believe I got it to work with the following:

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

[#7]

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

[Anonymous]

Hi

Is it like ..

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