Announcements
cancel
Showing results for
Did you mean:
Creator III

## Monthly Bar chart expression with 2 timestamps

Hi all,

I am working on a report where there are 3 timestamp fields. I have created a single date field "BDate" by mapping these 3 timestamp fields as shown in the picture-

I am facing an issue in creating the monthly chart (dimension is a month,BMonth), where I am dealing with 2 timestamp fields (Date2_Comp,Date3_Sched) in the expression. The expression looks like this-

=(Count({
<
BType={'Comp'}>
+
<
STATUS={'R'}, BType={'Sched'}, BDate = {'<\$(=(Max(BDate)+1))'} >
+
<
Date3_Sched = {'>=\$(=num(MonthStart(Max(BDate))))<\$(=(Max(BDate)+1))'}, Date2_Comp -= {'<\$(=num(MonthStart(Max(BDate))))'} >}
distinct CODE))

So, the first part of the expression  <BType={'Comp'}> works fine. It is going to pick up all the CODEs with respect to each month on the x-axis.

But, the 2nd & 3rd part of the expression has different conditions-

1.) <STATUS={'R'}, BType={'Sched'}, BDate = {'<\$(=(Max(BDate)+1))'} >

In this expression all those CODEs with BType=Sched and with BDate <= each month on x-axis has to be counted.

for eg. on month bar "Mar", it has to show a count of all CODEs where month(BDate)<=Mar (no condition on year)

and similarly with the 3rd part of the expression-

2.) <Date3_Sched = {'>=\$(=num(MonthStart(Max(BDate))))<\$(=(Max(BDate)+1))'}, Date2_Comp -= {'<\$(=num(MonthStart(Max(BDate))))'} >

Have both Date3_Sched and Date2_Comp here.

Thank you very much.