Webi 14.2.3 Count object between 2 Dates

[Former Member]

Hello Team,

Since this morning I try to find a solution to count object between two dates.

But I can't found a solution.

To explain you I created an Excel file with my data and a result tab. But I can't attach an Excel File.

Can you help me please ?

data-example.txt

[sateesh_kumar1]

Hi ,

Is start and End dates are separate columns in data ?

If yes ,below logic will work .

=Sum(If([Occurance Date]<=[End Date]) And (([Occurance Date]>=[Start Date])) Then ([Element]) ForEach([Incident]))

we can compare dates same way as in Excel !

Regards

Sateesh

[Former Member]

Hello Thanks a lot for your feedback, I have an issue regarding your answer

As you can see for me [End Date] in your formula =[Occurrence Date (Date Only)]

and [-28] = RelativeDate([Occurrence Date (Date Only)];-28)

=Sum(If([Occurrence Date (Date Only)]<=[Occurrence Date (Date Only)]And [Occurrence Date (Date Only)]>=[-28]) Then [NbIncident] ForEach([BT Incident]))

You think it is the reason that your formula = same value that [NbIncident]

[sateesh_kumar1]

Hi ,

what are the start and end dates ?

you are comparing same object ,[Occurrence Date (Date Only)]<=[Occurrence Date (Date Only)] . I think it is incorrect ,as it always be TRUE.

we should use only date object itself instead of number to compare .

Ex : [Occurrence Date (Date Only)]>=[-28] is incorrect .[Occurrence Date (Date Only)]>=relativedate([Occurrence Date (Date Only)];[-28]

Regards

Sateesh

[Former Member]

Hi,

Start Date is [Occurrence Date (Date Only)]-28days

End Date is [Occurrence Date (Date Only)]

I write again my formula, But I receive only the counting Incident related only the incidents created the [Query 2].[Occurrence Date (Date Only)]

=Sum(If([Query 2].[Occurrence Date (Date Only)]<= [Query 2].[Occurrence Date (Date Only)] And [Query 2].[Occurrence Date (Date Only)]>=RelativeDate( [Query 2].[Occurrence Date (Date Only)];-28)) Then ([Nb Incident] ForEach([BT Incident])))

It is possible to arrange a meeting (10min) to explain my request in my context ?

[sateesh_kumar1]

Now its interesting ! .

are you using 2 queries ? did queries merged ?