topic Re: Count with Firstsortedvalue in QlikView

Count with Firstsortedvalue

Anonymous — Fri, 28 Aug 2015 14:42:45 GMT

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 — Fri, 28 Aug 2015 17:54:01 GMT

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 — Fri, 28 Aug 2015 18:02:01 GMT

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)