Combo chart with count and cumulative count

I am trying to create a dual axis combo chart - one axis showing count per day (date) and the other axis showing cumulative count (over the full chart).

I have been trying to get runningsum to work but if I use something like:
runtotal=runningsum({id},[{date} ASC])
then I need to have: date, runtotal and id in the field wells to make the runtotal calculation work, and in fact ‘id’ needs to be in a dimension field well. The only place I can place it is in Group/Color since date is in the X axis field well. This makes the chart display but coloring by id ruins the chart. Additionally I can’t then add the id as a count for the lines measure as it can’t be used twice in the same chart. I guess I could duplicate it in data prep but that doesn’t sound like the best way???

I tried runtotal=runningsum({date},[{date} ASC]) but this results in a non-distinct count (on the date aggregation).

I’m sure it can’t be that complicated but it doesn’t seem to be intuitive.

Hi Simon, Is this what you are trying to achieve?

Cumulative Counts of ActiveItem = runningCount(count(ActiveItem),[Date ASC])

Hi Salim, this is close! but at least in my case this formula only counts periods (and only periods where there is data / not null). For example if I group by Date (month) over 1 year of data this produces a count of 12. I need the cumulative count of items in each month. Looks like the same is happening in your case as the line appears linear.

Simon, you can change the calculation to
Cumulative Counts of ActiveItem = runningSum(count(ActiveItem),[Date ASC])

1 Like