I want to create a clustered bar combo chart where on:
-
x axis i have date range (
datefield) -
bars i have
activefield (sum)
The goal is to sum over date range.
However, my dataset contains duplicate rows which are created by addition of group and division fields ( many-to-many relationship with owner_id field ).
I have approached this issue from two angles (please see image attached):
First approach:
In SQL I ranked the rows in order to identify duplicated rows created from addition of group and division fields and whenever rank is > 1 I replace duplicated values in the active field with 0.
Then in the QS visual layer I choose SUM on active field and on x axis we choose date field.
What happens: Ofc SUM works as expected BUT I have QS filter set up for group & division fields and whenever I choose group or division from the filter that WAS NOT in the row ranked as 1 (so it’s value for field active is equal 0 then I will always get 0 in the visual.
What I tried out:
- SumIf calculated field → (sumIf(active,{rank} = 1) → this doesn’t work, if I then choose group or division in the filter which was not in the row ranked as 1 i will see no results in the visual.
Second Approach:
Instead of replacing duplicated values with 0 in the active field. I decided to keep them and I tried to create the calculated fields:
- sumOver and max calculated field → sumOver(max(sum_active_over_date_range ) [date]) → this doesn’t work. If you then select MONTH drill up on date range (x axis), the value in the bar for a specific month will be a max value found for a day in the specific month. It doesn’t sum up values for whole month.
- sumOver calculated field and max in the field well → sumOver({sum_active_over_date_range},[{date},{group_name},{division_name}],PRE_AGG) and then choose max in the field wells (bars) → this also doesn’t work, my results in the bars are incorrect (too high)