Hello,
I have a calculated field that I need to use as a dimension in a pivot table. Below is the formula. I had these as individual calcs and attempted to combine them after referencing them individually in an if/else statement didn’t work due to not being able to add agg. fields to a group by. How can I get around this? Pushing this to SQL is not an option.
ifelse(
// 1. Has new PODs
distinct_count(
ifelse(
countOver(
ifelse(
orderdate >= addDateTime(-${UnsoldLookback}, ‘MM’, ${SoldPeriodStart})
and orderdate < ${SoldPeriodStart},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) = 0
AND
countOver(
ifelse(
orderdate >= ${SoldPeriodStart} and orderdate <= ${SoldPeriodEnd},
{customerid_no_credits_qty},
NULL
),
[{Invoice/OrderID}, salespersonid, Customerid],
PRE_AGG
) > 0,
Customerid,
NULL
)
) > 0,
“Has new PODs”,
// 2. Has Sales in Unsold Period
distinct_count(
ifelse(
countOver(
ifelse(
NOT (
orderdate >= addDateTime(-${UnsoldLookback}, ‘MM’, ${SoldPeriodStart})
and orderdate < ${SoldPeriodStart}
),
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
Customerid,
NULL
)
) > 0,
“Has Sales in Unsold Period”,
// 3. Credited PODs
distinct_count(
ifelse(
countOver(
ifelse(
orderdate >= addDateTime(-${UnsoldLookback}, ‘MM’, ${SoldPeriodStart})
and orderdate < ${SoldPeriodStart},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) = 0
AND
countOver(
ifelse(
orderdate >= ${SoldPeriodStart} and orderdate <= ${SoldPeriodEnd},
{Credited_Pod_Flag},
NULL
),
[productid, salespersonid, Customerid],
PRE_AGG
) > 0,
Customerid,
NULL
)
) > 0,
“Credited PODs”,
// 4. Credited non-PODs
distinct_count(
ifelse(
countOver(
ifelse(
orderdate >= addDateTime(-${UnsoldLookback}, ‘MM’, ${SoldPeriodStart})
and orderdate < ${SoldPeriodStart},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0
AND
countOver(
ifelse(
orderdate >= ${SoldPeriodStart} and orderdate <= ${SoldPeriodEnd},
{Credited_Pod_Flag},
NULL
),
[productid, salespersonid, Customerid],
PRE_AGG
) > 0,
Customerid,
NULL
)
) > 0,
“Credited non-PODs”,
// Sales Opportunity"
distinct_count(
ifelse(
countOver(
ifelse(
orderdate >= addDateTime(-${UnsoldLookback}, ‘MM’, ${SoldPeriodStart})
and orderdate < ${SoldPeriodStart},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) = 0
AND
countOver(
ifelse(
orderdate >= ${SoldPeriodStart} and orderdate <= ${SoldPeriodEnd},
{Invoice/OrderID},
NULL
),
[{Invoice/OrderID}, salespersonid, Customerid],
PRE_AGG
) = 0,
Customerid,
NULL
)
) > 0, “Sales Opportunity”,
“”
)