Set Analysis Less Than of Two Fields

[#1]

Hello:

I am trying to find the right syntax for doing a less than with two fields in set analysis.

This is what I want to include: onhand < job lot

The values represented in each field are typically between 0 - 20.

Any help here would be greatly appreciated. I've trid everything and cannot get the right syntax.

Thanks,

Ben D.

[MayilVahanan]

Hi

     Can you say the expression? Any distinct field in it.

     If so,

     Try like this,

     =Count({<Id ={"=onhand < joblot"}>} Id) In this, Id is distinct field

     or

     =Sum({<Year = {"<$(=Max(Year))"}>}Sales)

     Hope it helps

[#3]

This is how I have it right now:

Aggr(Count({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0}>}DISTINCT If(OnHand < job_lot, ItemKey)),POSDate,ItemKey)

Performace is horrible with this expression though. I need to find a way to get the correct syntax for adding the OnHand < job_lot into the count set analysis. Then I should be able to get rid of the aggregation, as well as the distinct.

Should look something like this

Count({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0},OnHand < {job_lot}>}ItemKey)

I just need the correct syntax. Thanks.

Ben D.

[MayilVahanan]

HI

Count({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0},ItemKey = {"=onhand < joblot"}>}ItemKey)

or

Count({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0},onhand ={"< joblot"}>}ItemKey)

Hope it helps

[#5]

Thank you for trying Mayil, but neither of these work.

Regards,

Ben D.

[MayilVahanan]

Hi

     Try like this,

     If(onhand < joblot,

          Aggr(

               Count({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0}>}DISTINCT ItemKey),POSDate,ItemKey))

     Can you post a sample file? and expected result..

[#7]

Hi Mayil:

Sorry this expression did not work either and unfortunately, I can't post a sample file.

I decided to create a flag in the script that checks for distinct item numbers.

Count

({$<POSDate={">$(=Date($(vMaxPOS)-($(vNumOfDays)-1)))<=$(=Date($(vMaxPOS)))"},inactive_flag={0},DistinctPOG={1}>}If(OnHand < job_lot, ItemKey))

This seems to work okay and performance is not too bad.

Thank you again for your help though!