Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I am building a chart that displays:
If(WeekDay(OrderCreationDate) > 5
,null()
,NetWorkDays(MonthStart(OrderCreationDate),OrderCreationDate)
) as OrderCreationWorkdayInMonth,
RangeSum(
Above(
Sum(
{<
Year=
,Month=
,Date=
,OrderCreationDate={">=$(=Date(MonthStart(Today(1))))<=$(=Date(Today(1)))"}
,RequestedDeliveryDateFromOrderLine={">=$(=Date(MonthStart(Today(1))))<=$(=Date(Floor(MonthEnd(Today(1)))))"}
>}
_OrderIntakeQty
)
,0
,$(=NetWorkDays(MonthStart(Today(1)-1),Today(1)))
)
)
/1000000
([Order Intake CM Year-1]+[Order Intake CM Year-2])/2
[Order Intake Current Month]/[Avg Order Intake CM last 2 years]
The formula for the first expression is rangesum where I have limited the calculation until workday of today-1 through set analysis, but because the 2-year average has to be displayed for the full month, the aggregation of the first expression continues for all displayed dimension values. Basically I want the part that I have marked with the red square not to be displayed.
If I turn the first expression into an IF function where I say "IF(RangeSum() < 2, null(), RangeSum())" they are indeed not being displayed when looking at the totals, but of course this limit of 2 isn't dynamic at all, so when selections are being made it doesn't work anymore. I tried to come up with a way to change this 2 by a dynamically calculated value relative to the RangeSum() but I haven't found a good calculation yet.
So I see two options:
Thanks!