Count Distinct for unique ID in Scorecard - the data response has a bad reaggregation?

[daann8]

Hi, I have been using Looker Studio for a short time and I have a question about the count_distinct function in a given time interval.
I have a Snowflake table data source connected to Looker with the monthly status of each user and some flags are assigned to them (for example, whether the user is churned).
This is what the data source looks like:

I would need to add a Scorecard type visual to the dashboard, which will tell me the unique number of users meeting a given flag in a defined timeframe. I'm using the count_distinct function before the user id for this, but the Scorecard visual gives me the error "The data response has a bad reaggregation".

The problem I have is that when I put the metric into a table, it shows me the number of unique ids in the period, but Scorecard gives me the sum for that period (I would need something like select count(distinct) from table where churn_flag = 'Y' and ts_date between x and Y).

Could you help me please? 🙂

[matyaslustig]

Same problem here!

[Mehdi_Oudjida]

@daann8 

You can try to apply something like the formula below, keeping in mind that the aggregation needs to be the last thing in the logic.

COUNT_DISTINCT(IF (churn_flag = 'Y'  AND ts_date between X and Y , COURIER_ID, NULL))

X and Y respecting the format "YYY-MM-DD"

I hope it helps.

Mehdi

[daann8]

Thanks for reply, but this one still doesen't give me only a unique IDs in a given period (and also I don't want to specify date interval in a metric).
I'm aplying this formula:
COUNT_DISTINCT(IF (CHURN_FLAG = 'Y' AND TS_DATE between date(2024,1,1) and date(2024,12,1) , COURIER_ID, NULL))

And it return me a SUM of count_distinct for each month.

[Mehdi_Oudjida]

@daann8 

Ok, so you can remove the condition on the date range in the formula
COUNT_DISTINCT(IF (churn_flag = 'Y'  , COURIER_ID, NULL))

Please, could you try to:
- test the same with an ended date range (sanity check)
- refresh your data with the button at the top right on the LS screen
- copy paste the scorecard and switch on a table chart, adding the date to be sure that you are on the same date range than the date picker
- In the settings tab of the chart configuration, check that a date field from the data source is selected to respect the date range 

Hoping that these tests will help you to understand the problem.

Mehdi

[daann8]

@Mehdi_Oudjida 

Thanks for your help. It works great if I don't specify any date range, but if I apply Year to Date, then it doesen't work - as you can see on screenshot. And also I have data on monthly granularity and Date Range Control can't be set only on months and years, but I'm not sure if its related to the problem what I have.

[maerlin9]

I also have problems with COUNT_DISTINCT returning too high values in conjunction with a date range...

[Mohanrao]

I resolved the issue by utilizing parameters in Looker Studio, where users can input the start date and end date. These input values are stored in parameters, and I then used the PARSE_DATE() function to convert the string inputs into date format. By doing this, I was able to use the user-provided dates dynamically within my formulas. 

This approach worked well and allowed me to incorporate the user-inputted date range into my calculations. However, it's more of a partial solution, as it requires manual input from the user rather than fully automating the process with a date range control.