Hi All,
I have a small case that i think it can be done by using MDX query.
I would like to find members that do not have any sales for at least 4 consecutive weeks within last 24 weeks .
Does anyone can help giving an idea how to solve it?
I have a solution to find members that don't have any sales for 4 weeks or more (but not for 4 consecutive weeks). below is the code:
with
set [last 24 weeks] as
TAIL(
FILTER(
[Dim Date].[FiscalYear].[Fiscal Week].members,
not isempty( [Dim Date].[FiscalYear].currentmember)
)
,24
)
set [Member no sales more than 4 wk]
FILTER(
[Dim member].[member ID].children ,
SUM(
[last 24 weeks],iif(isempty([Measures].[NetSales]),1,0)
) >= 4
)
select
{
[Member no sales more than 4 wk]
} on rows,
{
[last 24 weeks]
} on columns
from [Test]