We have implemented the calculated field* `Total Population = distinct_countOver(ordernumber, [workflowname], PRE_FILTER), which is currently working as expected. However, the value reflects the overall count and does not adjust based on the selected date range. Since we have several filters applied in the analysis, our requirement is that this calculated field should respond only to the From and To date controls (connected to the StartDate and EndDate parameters via the DateDetails calculated field), while remaining unaffected by other filters. Could you please assist us in ensuring that the Total Population field is impacted solely by the selected date range and not by additional filters?
Hi @tdr_Dinesh,
Unfortunately this is a common scenario that results in miss-matched aggregations; there are a couple work arounds you could test out.
First would be to move your initial total population calculation to the dataset level, that way you can avoid the pre filter portion
From a Calc. field option, I don’t have a similar dataset to demo this out but potentially try something along these lines:
First, start by creating this calculation that you can then use in the next: Date Filtered - ifelse(
{your_date_field} >= ${StartDate} AND {your_date_field} <= ${EndDate},
{ordernumber},
NULL
)
Then, you can use in the following: Total Population = distinct_countOver({Date Filtered}, [{workflowname}], PRE_FILTER)
This way, you’re evaluating the date filters in the if else prior to the calculation. The clearest way to handle this though would be by moving to the dataset.
We have already created a calculated field at the dataset level Where ordercount=distinct_count(orderNumber). Based on the recommendation, we also implemented the date-filtered calculation. However, we are currently encountering the following error message.
Hi @tdr_Dinesh,
I’m not quite sure this is causing your error but one thing to point out…in the top row, it looks like you’re using ‘date details’ as a calculated field and in the bottom row, it looks like you’re using a parameter called ‘DateDetails’.
Let me know if that alters anything
Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.
Looking at your scenario, this formula that I came up with could be something to try out as well if you are still looking for a solution:
If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
That is great to hear! If you’re willing, please feel free to post what your solution was here to help out other community users if they run into the same issue.
