topic Re: Set Analysis Less Than of Two Fields in QlikView

Set Analysis Less Than of Two Fields

Tue, 31 Jul 2012 20:01:35 GMT

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.

Re: Set Analysis Less Than of Two Fields

MayilVahanan — Tue, 31 Jul 2012 20:08:26 GMT

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

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

Hope it helps

Re: Set Analysis Less Than of Two Fields

Tue, 31 Jul 2012 20:20:38 GMT

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.

Re: Set Analysis Less Than of Two Fields

MayilVahanan — Tue, 31 Jul 2012 20:44:53 GMT

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

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

Hope it helps

Re: Set Analysis Less Than of Two Fields

Wed, 01 Aug 2012 16:34:19 GMT

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

Regards,

Ben D.

Re: Set Analysis Less Than of Two Fields

MayilVahanan — Wed, 01 Aug 2012 16:48:52 GMT

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

Re: Set Analysis Less Than of Two Fields

Thu, 02 Aug 2012 13:24:45 GMT

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!