Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Include Nulls in Set Analysis

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

5 Replies
Highlighted

May be this:

RangeSum(

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

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

Highlighted

try like this

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

+

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

Highlighted

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

Highlighted
Partner
Partner

Try like below

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

Highlighted

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