Include Nulls in Set Analysis

[#1]

Hi,

I am trying to write a set something like this:

Sum ( {< PlanDate = { " > = $(vMinPlanDate) < = $(vMaxPlanDate) " } , HiringDate= { " > = $(vMinHireDate) < = $(vMaxHireDate) " } >}Sales)

which returns the sales for all records where Plan date falls int he vMinPlanDate and vMaxPlanDate  and HireDate Falls in range vMinHireDate and vMaxHireDate.

The problem is that it doesnt include the data where any of the date (hire date or plan date) is null. I need to include the records with null dates. I have followed using various blogs and discussions but the approach doesnt work in this case.

Please suggest.

Thanks,
Deepak

[sunny_talwar]

May be this:

RangeSum(

Sum({<PlanDate = {">=$(vMinPlanDate) <=$(vMaxPlanDate)"}, HiringDate = {">=$(vMinHireDate)<=$(vMaxHireDate)"}>} Sales),

Sum(If(Len(Trim(PlanDate)) = 0 or Len(Trim(HiringDate)) = 0, Sales)))

[avinashelite]

try like this

Sum ( {< PlanDate = { " > = $(vMinPlanDate) < = $(vMaxPlanDate) " } , HiringDate= { " > = $(vMinHireDate) < = $(vMaxHireDate) " } >}Sales)

+

Sum ( {< PlanDate -= { "*" } , HiringDate -= { "*" } >}Sales)

[Peter_Cammaert]

Ah, tricky! I guess that while a NULL date value will match one conditiion, the other non-NULL date should still fall within the set period for that date field...

And what about records with both dates = NULL (you said "...where hire date or plan date is null")?

[perumal_41]

Try like below

Sum ( {< PlanDate = { " > = $(vMinPlanDate) < = $(vMaxPlanDate) " }  , HiringDate= { " > = $(vMinHireDate) < = $(vMaxHireDate) " } > +  < PlanDate = { "  = Len(Trim(PlanDate)) = 0 " }  , HiringDate= { "  = Len(Trim(HiringDate)) = 0 " } > }Sales)

[Peter_Cammaert]

A quick hack could be (3 sets):

=Sum({<PlanDate={">=$(vMinPlanDate)<=$(vMaxPlanDate)"},HiringDate={">=$(vMinHireDate)<=$(vMaxHireDate)"}> +

<PlanDate={ ">=$(vMinPlanDate)<=$(vMaxPlanDate)"},SalesOrderID={"=Len(Trim(HiringDate))=0"}> +

<SalesOrderID={"=Len(Trim(PlanDate))=0"},HiringDate={">=$(vMinHireDate)<=$(vMaxHireDate)"}>} Sales)

Replace SalesOrderID with the identifier field of your sales orders.

Not very elegant though...