Error creating sum of partitioned calculated field

Q&A
, ,
1

Hi all,
I have a dataset structured in this way:
settle_date, user, turnover, grosswin

Dataset is loaded with last 6 months data based on settle_date.

I need to show a KPI with today’s Margin and the difference percenteage vs last 6 months same weekday average(i.e if today is monday, I need to calculate the average margin for all the mondays in last 6 months)

I created a calculated field to extract the settle_date with the same weekday as of today (settle_date_weekday). For other weekdays returns null.

To calculate daily margin I just have a calculated field as sum(grosswin)/sum(turnover).

To calculate the average, I start trying to calculate the sum of all margins partitioned by settle_date_weekday

sumOver(sum({Grosswin EUR})/sum({Turnover EUR}),[{settle_date_weekday}])

however I am getting error “Table calculation attribute reference are missing in field wells” if I do not include settle_date_weekday in dimensions.

How can I calculate the above and show in a single KPI?

2

Since the “settle_date_weekday” is required for the calculation, you could potentially added to the Single KPI visual as a “Trend Group”. If you want to remove the trend visual created, then you can edit the visual (Format Visual) and hide the “visual” and the “secondary value”.

3

Hi @nicola,
It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

4

Hi @nicola,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you