topic Re: Feasibility of use of set analysis in QlikView

Feasibility of use of set analysis

Sat, 23 Jul 2016 12:49:36 GMT

I am trying to count a certain number of orders based on a condition where the sum of required completion date and a certain number of days is less than a certain defined date. To be precise, the expressions is

=count(distinct if([Required Comp Date]+[No.of Days] <= Date($(x),'DD/MM/YYYY'), Orders))

[Required Comp Date] and [No. of Days] are fields there in the data model.

Is it possible to construct this statement using set analysis ?

Re: Feasibility of use of set analysis

vishsaggi — Sat, 23 Jul 2016 19:44:42 GMT

I dont know how feasible it is but is it possible for you to create a new field in Load statement for date+days like

Load col1,

col2,

col3,

Date([Required Comp Date], 'DD/MM/YYYY') + [No.OfDays] AS [Required Comp Date_Days]

FROM ..... ;

And In expression

Expr: =count(distinct {< [Required Comp Date_Days] = { "<= $(=Date(x,'DD/MM/YYYY')) " } >}, Orders))

Let me know if this works I might have to tweak around the set code.

Thanks,
V.

Re: Feasibility of use of set analysis

vinieme12 — Sat, 23 Jul 2016 20:56:35 GMT

Try this

vDatevar = NUM(the date you want to compare with)

=count(DISTINCT {<OrderID = {'=NUM([Required Comp Date]+[No.of Days])<$(vDatevar)'}>} OrderID)

Instead of comparing dates compare the numeric values of the dates, it's much more efficient and hassle free to make the formatting right.

Re: Feasibility of use of set analysis

sunny_talwar — Sat, 23 Jul 2016 22:34:06 GMT

I think there is not need to add any formatting here. May be try just this:

=Count(DISTINCT {<Order = {"=[Required Comp Date] + [No.of Days] <= x"}>} Orders)

Re: Feasibility of use of set analysis

Sun, 24 Jul 2016 03:44:13 GMT

Thanks all for your replies !!

@vishsaggi

That is a solution I had thought of, but creating a separate field in the data model for an expression used once or twice pushed me back.

I will surely delete the other duplicate thread.

@Vineeth Pujari and Sunny T

Both solutions are working for me however, I am surprised to see that the expression

count(distinct {<OID={"=(RCD+GP)<=Date('2016-08-10','DD/MM/YYYY')"}>} OID)

is working even though it compares a number (I checked (RCD+GP) for an OID in a text object and it returned the date in number format) with a date (10/08/2016).

I would definitely try both to see if there is any performance difference, since I have a huge dataset. Will post an update on the same soon.

Re: Feasibility of use of set analysis

vinieme12 — Sun, 24 Jul 2016 11:35:07 GMT

If you have a huge data set you are better off adding the additional field in the table , because simpler the expression faster your dashboard.

Cheers