why the value of an expression gives another result in FirstSortedValue

[ali_hijazi]

Hello

I got a qlikview document; the schema contains the following fields Country, and each country has several brands and for each brand I got a calendar for seasons in which for every season there is a date called LastDateB4Sales

now if I create a chart with Country Dimension and put the following expression with the season selected I get correct results

aggr(only({<Season={$(vGroup1Season)}>}LastDateB4Sales),Country, Season, Brand))

for example:

Country1             41175

Country2             41176

Now I get the following expression which is get the first UnitRetail from transactions whose date <= LastDateB4Sales

Aggr

(

FirstSortedValue

({<

          H_RetFlag={'F4106'}

          , Season={$(vGroup1Season)}

          ,[Effective Date]={">$(aggr(only({<Season={$(vGroup1Season)}>}LastDateB4Sales),Country, Season, Brand)))"}

          ,DocType_TransType={'IN','OUT'}

>}

  [Unit Retail]

, -H_BPDATE

)

The above expression is giving me wrong numbers but when I put instead of the blue expression the exact value which is 41175 or 41176 I get on the chart the correct answer

why the FirstSortedValue function is affecting on the result of the blue expression?

Please advise

[danielrozental]

First. your expression should not start with AGGR, that makes no sense really. Neither does using AGGR in a set expression nor trying to use set analysis to get every products LastDateB4Sales value since set analysis doesn't get resolved independently for each record.

If you try posting a sample qvw we might be able to help you.

[ali_hijazi]

Ok the big AGGR is inside a Sum

but what about the small in the set analysis?

[er_mohit]

try use of  '-' sign which value you want to see in above expression

aggr({<Season={$(vGroup1Season)}>}LastDateB4Sales),-Country, Season, Brand)))"}

hope it helps

[ali_hijazi]

what does the '-' sign mean here?

[er_mohit]

use nagative sign because for firstsortedvalue  expression it always give ahead from that value based which you want so, put - sign on that field name.