Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
paperjam
Contributor II
Contributor II

Synthetic dimension with calculated measure - not working

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!

Labels (1)
2 Replies
paperjam
Contributor II
Contributor II
Author

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))

marcus_sommer

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.