topic Re: Include Nulls in Set Analysis in QlikView

Include Nulls in Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Include Nulls in Set Analysis

sunny_talwar — Tue, 19 Apr 2016 12:55:07 GMT

May be this:

RangeSum(

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

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

Re: Include Nulls in Set Analysis

avinashelite — Tue, 19 Apr 2016 12:59:48 GMT

try like this

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

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

Re: Include Nulls in Set Analysis

Peter_Cammaert — Tue, 19 Apr 2016 13:10:19 GMT

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")?

Re: Include Nulls in Set Analysis

perumal_41 — Tue, 19 Apr 2016 13:18:26 GMT

Try like below

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

Re: Include Nulls in Set Analysis

Peter_Cammaert — Tue, 19 Apr 2016 15:22:46 GMT

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