Please see the below screenshots. I’m having an issue where my pivot table works fine when I add the Cases field to the value well ( a row level calculation computed in sql) and have the aggregation set to sum, but when I add an additional measure it (any measure)
removes multiple invoices. I’ve replaced any group by fields with null with placeholder values. I do have two complex fields in the group by as follows that don’t seem to be the issue because when i remove them the formulas still don’t work together.
The odd thing is when i filter for customer, it maintains all of the records i need. A bit about the dataset that might be helpful: It is a customer/salesperson dataset with a cross joined products table. Products is in columns of the pivot table.
Here the only non standard group by fields although as previously mentioned that don’t seem to be causing the issue as well as the screen shots. Any help is appreciated!
Monitoring Periods
ifelse(depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
concat(’ Sold:', ’
', formatDate(${SoldPeriodStart}, ‘MM-dd-yyyy’), ’ ', formatDate(${SoldPeriodEnd}, ‘MM-dd-yyyy’)),
depletiondate >= addDateTime(- ${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart}) and depletiondate < ${SoldPeriodStart},
concat(‘Unsold:’, ’
', formatDate(addDateTime(- ${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart}), ‘MM-dd-yyyy’), ’ ', formatDate(addDateTime(-1, ‘DD’, ${SoldPeriodStart}), ‘MM-dd-yyyy’)) ,
depletiondate < addDateTime(- ${UnsoldLookBack},‘MM’, ${SoldPeriodStart}), concat(‘Prior Unsold:’, ’
', ‘Before’, ’ ', formatDate(addDateTime(- ${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart}), ‘MM-dd-yyyy’)),
depletiondate > ${SoldPeriodEnd}, concat(‘Post Sold:’, ’
', ‘After’, ’ ', formatDate(${SoldPeriodEnd}, ‘MM-dd-yyyy’)),
“Never Sold”)
Category:
ifelse(
// 1. Has new PODs
distinctCountOver(
ifelse(
sumOver(
ifelse(
depletiondate >= addDateTime(-${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart})
and depletiondate < ${SoldPeriodStart},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) <= 0
and
sumOver(
ifelse(
depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
Customerid,
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
“Has new PODs”,
// 2. Credited PODs
ifelse(
distinctCountOver(
ifelse(
sumOver(
ifelse(
depletiondate >= addDateTime(-${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart})
and depletiondate < ${SoldPeriodStart},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) <= 0
AND
countOver(
ifelse(
depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) >= 1
AND
sumOver(
ifelse(
depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) <= 0,
1,
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
"Credited POD",
//Credited Non POD
ifelse(
distinctCountOver(
ifelse(
// 1. sales in the unsold period
sumOver(
ifelse(
depletiondate >= addDateTime(-${UnsoldLookBack}, ‘MM’, ${SoldPeriodStart})
and depletiondate < ${SoldPeriodStart},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0
AND
// 2. At least one transaction in the sold period
countOver(
ifelse(
depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
{Invoice/OrderID},
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) >= 1
AND
// 3. Net cases in sold period <= 0 (full return or more)
sumOver(
ifelse(
depletiondate >= ${SoldPeriodStart} and depletiondate <= ${SoldPeriodEnd},
Cases,
0
),
[salespersonid, Customerid, productid],
PRE_AGG
) <= 0,
1, // -- dummy countable value
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
“Credited Non-POD”,
// 4. Has Sales in Unsold Period
ifelse(
distinctCountOver(
ifelse(
sumOver(
ifelse(
depletiondate >= addDateTime(-${UnsoldLookBack}, 'MM', ${SoldPeriodStart})
and depletiondate < ${SoldPeriodStart},
Cases,
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
Customerid,
NULL
),
[salespersonid, Customerid, productid],
PRE_AGG
) > 0,
"Has Sales in Unsold Period",
"Sales Opportunity"
)
)
)
)