Monthly Bar chart expression with 2 timestamps

[divya_anand]

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.

Can someone please help me in dealing with these 2 issues.

Thank you very much.

[Gysbert_Wassenaar]

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

You mean it has to ignore the BMonth dimension value and show records of all the earlier months? For that you'll need either a rather complex expression or as AsOf table. See here for more information: Calculating rolling n-period totals, averages or other aggregations

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

Have both Date3_Sched and Date2_Comp here.

No idea, I'd have to have a look at the data. I suspect that you'll run into the problem that the set is calculated at the chart level, not the row level. And that's assuming there are even records that have both Date2_Comp value and a Date3_Sched value.