Hi!
I have a data set with the following columns (these columns are just for illustrative purposes - my actual columns are different):
the_week,shape,color,count,customer_name,location
The count changes based on shape and color, but not based on customer_name or location.
What I want is a calculated field {Total count} that gives me the sum of the maximum value of count for each group (the_week, shape, color).
This calculated field must work even if all columns are not present in the visual. For instance, if the visual is a table with only the_week as grouping column, then {Total count} should be the sum of max counts within each group (the_week, shape, color) for that week.
Hereās some example tables showing what Iām looking for. The current Total count is way off.
Iāve tried many different things, none of which have been successful. My current calculated field has this:
sum(
ifelse(
rank([{the_week} DESC], [{the_week}, color, shape], PRE_AGG) = 1,
maxOver(
count,
[{the_week}, color, shape],
PRE_AGG
),
0
)
)
Thanks in advance!