Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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.
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)
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.
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
V