Working Day sales to include weekend sales for Monday

[wcilliers]

Hi Experts,

I am trying to calculate sales per day, but only for trading days. (Excl weekends and public holidays).

However, I need to include Saturday and Sunday sales for Monday.

This is what I have, but obviously not working....

If(WeekDay = 'Mon',Sum({<CalDate = {'>=$(=(CalDate-2))<$(=Date(CalDate))'}>}SalesAmount),

Sum(SalesAmount*WorkingDay))

Working Day is my 0 and 1 indicator for when it is a working day, which works fine. But my Monday calc doesn't work.

Any ideas?

Thanks in advance!

Wynand

[tresesco]

Have you checked if the WeekDay field value is coming as string or as number by default? Where are you trying this expression, in chart or textbox?

[wcilliers]

Hi Tresesco,

The Weekday field is a string field. The calc works for all days except Mondays. Just gives me zero.

Thanks,

Wynand

[tresesco]

If so, it could be because of date format mismatch, try like:

If(WeekDay = 'Mon',Sum({<CalDate = {'>=$(=Date(CalDate-2))<$(=Date(Max(CalDate)))'}>}SalesAmount)...

[wcilliers]

Hi Tresesco,

I have tested now and it actually doesn't work. It calculates fine, but the smaller than and greater than doesnt seem to have an effect on it... So it just calculated monday the same as the other days.

Wynand