Skip to main content
Solved

Countuniqueof with several dimension

  • June 17, 2025
  • 4 replies
  • 99 views
J
Apprentice Author

Hello,

I need to count the number of client inside a Database by several Dimension (Month, Business Unit, Category(New, Upsell, downsell, Churn), Client type, ...)

I tried using COUNTUNIQUEOF but it doesn’t work (timeout of request).

IF(Month < 'Switchover Date for Forecast/Budget',IF('Full data BI'.subscription_type = 'Subscription Type'."Renewable" AND 'Full data BI'.segment_revenue_category <> 'Customer Category'."Churn",
COUNTUNIQUEOF('Full data BI'.customerid) [by :'Full data BI'.segment, 'Full data BI'.segment_revenue_category,'Full data BI'.Month, 'Full data BI'.customer_type, 'Full data BI'.product, 'Full data BI'.product_type, 'Full data BI'.region, 'Full data BI'.subscription_type]))

Could you please help me sort that out ?

Best answer by Benoit

Can you maybe try this in the formula playground in auto mode:

 

IF(Month < 'Switchover Date for Forecast/Budget',

'Full data BI'.customerid

[filter: 'Full data BI'.subscription_type = 'Subscription Type'."Renewable"]

[exclude: 'Full data BI'.segment_revenue_category = 'Customer Category'."Churn"]

[by countunique :'Full data BI'.segment, 'Full data BI'.segment_revenue_category,'Full data BI'.Month, 'Full data BI'.customer_type, 'Full data BI'.product, 'Full data BI'.product_type, 'Full data BI'.region ]))

 

Please let me know.

Thanks

 

4 replies

Benoit
Community Manager
Forum|alt.badge.img+15
  • BenoitCommunity Manager
  • Community Manager
  • June 17, 2025

Hi,

You most probably need to use a countunique modifier instead of the COUNTUNIQUEOF function.

Maybe try something like this:

IF(Month < 'Switchover Date for Forecast/Budget',IF('Full data BI'.subscription_type = 'Subscription Type'."Renewable" AND 'Full data BI'.segment_revenue_category <> 'Customer Category'."Churn",

'Full data BI'.customerid [by countunique :'Full data BI'.segment, 'Full data BI'.segment_revenue_category,'Full data BI'.Month, 'Full data BI'.customer_type, 'Full data BI'.product, 'Full data BI'.product_type, 'Full data BI'.region, 'Full data BI'.subscription_type]))

 

Hope this helps!

Best,

 

Benoit - Pigment wizard
J
Apprentice Author
  • JB BuffetApprentice Author
  • Author
  • Apprentice Author
  • June 17, 2025

Hello, formula seems to be working but i have this error message : 

The formula reached 201 G cells during one of its execution steps, exceeding the maximum size allowed (1 G)

I have 450k lines in my database, do you think it could be due to its size ? 

Thanks again for your help !!

 

 

Benoit
Community Manager
Forum|alt.badge.img+15
  • BenoitCommunity Manager
  • Community Manager
  • Answer
  • June 17, 2025

Can you maybe try this in the formula playground in auto mode:

 

IF(Month < 'Switchover Date for Forecast/Budget',

'Full data BI'.customerid

[filter: 'Full data BI'.subscription_type = 'Subscription Type'."Renewable"]

[exclude: 'Full data BI'.segment_revenue_category = 'Customer Category'."Churn"]

[by countunique :'Full data BI'.segment, 'Full data BI'.segment_revenue_category,'Full data BI'.Month, 'Full data BI'.customer_type, 'Full data BI'.product, 'Full data BI'.product_type, 'Full data BI'.region ]))

 

Please let me know.

Thanks

 

Benoit - Pigment wizard
J
J
Apprentice Author
  • JB BuffetApprentice Author
  • Author
  • Apprentice Author
  • June 19, 2025

It’s working well like that ! Thanks a lot for your help :) 

Benoit

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement