Count with Firstsortedvalue

[Anonymous]

Hi all,

Could someone take a look at the below, %_VNO is the vendor account number.

I'm trying to count UNIT.REGNO if the %_VNO is the second seller based on the sale date (UNIT.SOLD_DATE)

count({<%_VNO={"$(=firstsortedvalue(%_VNO,UNIT.SOLD_DATE ,2))"}>} UNIT.REGNO)

Thanks

[swuehl]

What do you mean with 'second seller based on the sale date'?

Could you post some sample data records, best by posting a small sample QVW and your expected result?

[sinanozdemir]

Hi Gareth,

I think you want to use Rank() function and aggregate it over Vendor No and Date dimensions:

count({<%_VNO={"=Aggr(Rank(Sum)), %_VNO, UNIT.SOLD_DATE) = 2"}>} UNIT.REGNO)

Hope this helps.

And of course, if you want to ignore all user selections, just add 1:

count({1<%_VNO={"=Aggr(Rank(Sum)), %_VNO, UNIT.SOLD_DATE) = 2"}>} UNIT.REGNO)