Hi - running into some difficulty when trying to use distinctCountOver. I am using this with a calculated field that I have partitioned by a calculated field called Date. Date uses a parameter called DateGrain to change the aggregation of the dates.
Here is the formula that is giving the error:
distinctCountOver(session, [{overall_rating},Date], PRE_AGG)/distinctCountOver(session,[Date], PRE_AGG)
And here is the “Date” calculated field.
Date:
ifelse(
${DateGrain} = ‘1. Day’,truncDate(“DD”,{Session Date EST}), ${DateGrain2} = ‘2. Week’,truncDate(“WK”,{Session Date EST}),
${DateGrain} = ‘3. Month’,truncDate(“MM”,{Session Date EST}),
${DateGrain} = ‘4. Quarter’,truncDate(“Q”,{Session Date EST}),
${DateGrain} = ‘5. Year’,truncDate(“YYYY”,{Session Date EST}),{Session Date EST}
)
Note - I’ve tried this as both a regular analysis calculated field AND a dataset field. I get the same result for both. Also, when I create the calculation and add “Date” it does include the curly braces - {Date}, but when I go back to edit the calculation they are gone.
Do you have a second parameter called DateGrain2? It’s in your “Date” calculated field.
Can you change your “Date” calculated field to something simpler like this and see if you still get the error? This would be a test to help us figure out if the issue is with the parameters.
Hey David - Yeah I had two parameters (I was going back and forth between dataset / analysis parameters in order to try everything), I removed DateGrain2 in the calculated field though (after I pasted it in the above thread).
If I update the formula with your suggestion it does indeed work.
Is there an update on this? I’m also trying to use distinctCountOver with an ifelse statement in the measure/dimension argument and it’s giving SQL errors when placed into a table visual. The issue does not occur when using countOver instead of distinctCountOver.
I’ve verified it happens with any ifelse statement in the measure/dimension argument, even ones that return the same field regardless of the condition (checking with things like ifelse(1=1, 1, 1)).
@dsost, this seems like a weird limitation of distinctCountOver or a bug. It doesn’t make sense that it works with countOver but not with distinctCountOver…
Hope everything is well! I saw that this thread has not received a response in a while. Were you able to find a different workaround in the meantime, or are you still running into persistent issues with your use case? If we do not hear back in 3 business days, I’ll close out of this topic.
