topic Re: Synthetic dimension with calculated measure - not working in App Development

Synthetic dimension with calculated measure - not working

paperjam — Thu, 31 Oct 2024 07:13:52 GMT

Hi there

I would like to get all the customers which made a purchase 1-time, 2-times, etc. YTD based on a quarter Filter. So basically the formula gets all the data up to the selected quarter. So far, so good. All my formulas are working:

Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} If(Aggr(Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} CustomerID), CustomerID) = 1, CustomerID))

Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} If(Aggr(Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} CustomerID), CustomerID) = 2, CustomerID))

etc.

But if i want to combine this logic with a synthetic dimenos (via ValueList), only 1-time customer works, but not 2-time customer etc.:

Works:
If(
ValueList('1-time customer', '2-time customer', '3-time customer', '4-time customer', '4-time+ customer') = '1-time customer',
Count(
{<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>}
If(
Aggr(
Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} CustomerID),
CustomerID
) = 1,
CustomerID
)
)
)

Doesn't work:
If(
ValueList('1-time customer', '2-time customer', '3-time customer', '4-time customer', '4-time+ customer') = '2-time customer',
Count(
{<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>}
If(
Aggr(
Count({<Quarter= {"<=$(=Max(Quarter))"}, Year= {"$(=Max(Year))"}>} CustomerID),
CustomerID
) = 2,
CustomerID
)
)
)

Doesn't seem logic to me that one time it works and the other time with exact the same logic it doesn't....

Thanks for your help!

Re: Synthetic dimension with calculated measure - not working

paperjam — Thu, 31 Oct 2024 08:31:12 GMT

I found a pretty good answer, but not a 100% the one I'm looking for:
https://www.youtube.com/watch?v=_AvyITll9mM

This works for not dynamic filter. But if I want to have my count on CustomerID based on filter, it doesn't work. How can I adjust my script so that CustomerID's are filtered according to the Quarter and Years?:

pick(Match(ValueList($(vCustomerTyp)),$( vCustomerTyp)),

Count(distinct {< Quarter = {"<=$(=Max(Quarter))"}, Year = {"$(=Max(Year))"}, CustomerID = {"=Count(CustomerID) = 1"}>} CustomerID),

Count(distinct {< Quarter = {"<=$(=Max(Quarter))"}, Year = {"$(=Max(Year))"}, CustomerID = {"=Count(CustomerID) = 2"}>} CustomerID),

Count(distinct {< Quarter = {"<=$(=Max(Quarter))"}, Year = {"$(=Max(Year))"}, CustomerID = {"=Count(CustomerID) = 3"}>} CustomerID),

Count(distinct {< Quarter = {"<=$(=Max(Quarter))"}, Year = {"$(=Max(Year))"}, CustomerID = {"=Count(CustomerID) = 4"}>} CustomerID),

Count(distinct {< Quarter = {"<=$(=Max(Quarter))"}, Year = {"$(=Max(Year))"}, CustomerID = {"=Count(CustomerID) > 4"}>} CustomerID))

Re: Synthetic dimension with calculated measure - not working

marcus_sommer — Mon, 04 Nov 2024 12:36:49 GMT

I think I would use an approach like:

class(aggr({ Set } count({ Set } Field), Dim1, Dim2), $(var))

whereby the variable var provides a dynamically adjusting of the cluster-size.

Class() is dual() and you may embed it in a pick-logic like:

pick(match(num(class()), 1, 2, 3), 'a', 'b', 'c')

The class() bucket-size is always equally to the specified second parameter - maybe the mentioned variable - but by embedding the count() into a rangemin(rangemax()) you may also fetch the lower/upper outliers.