Combo chart with count and cumulative count

[SimonW]

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.

[salim]

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

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

[SimonW]

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.

[salim]

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