How to combined 2 aggregated calculated field together

Question & Answer
, , , , , ,
1

I have calculated the below calculated field which basically given in the latest month forecasted value.
(A)
ifelse(month - {latest month numbner} = 0 AND year =2023, {forecatsed_sales}, 0)

Now what i want to do is multiply the output of the above formula into “#ofdays so far” divided by “total #ofdays in that month”

for example if today is 1st August then
output of the formula *1/31

for this i again created 2 calculated fields

(B)“total #ofdays in that month”
distinct_countIf({order_day},month - {latest month numbner} = 0 AND year =2022)

(C)“#ofdays so far”
distinct_countIf({order_day},month - {latest month numbner} = 0 AND year =2023)

Finally i want A*B/C

but it throwing an error saying that "Mismatched aggregation. Custom aggregations can’t contain both aggregate “COUNT” and non-aggregated fields “COUNT(DISTINCT CASE WHEN “b319fdf5-3caf-4170-8174-d4f4edefbb27” - “08710443-490d-4937-955b-46dec9399505” = 0 AND “1080f182-4531-4c3d-9e54-833d16b836ff” = 2023 THEN “4bade08b-503c-4bc1-a3b0-e5b5bd9d6cdf.order_day” ELSE NULL END)”, in any "

can you please help how to do this

2

Hi @Aditya_Raj !
If I understand correctly, calculations A, B and C are all valid and successfully create. The issue is A*B/C. If that’s true, I think adding an aggregation (min, max, sum, etc) around the forecasted sales in expression A will alleviate the error message when you combine all three expressions. Please let me know if that solves the issue.

3

It worked thank you so much!