4 Replies Latest reply on Jun 19, 2018 11:47 PM by dmitry.beransky

# Calculating a total for the visible date range

For my data I want to show a running total chart for a filtered date range.  To make sure the total line doesn't start at zero for the range, I used the trick of converting a date from a dimension to a measure ( LOOKUP(min([date]), 0) ).  Now, I also want to show two columns: the total for all of the data and the total for just the visible/filtered range.  I can do the overall total, but I can't figure out how to do the total for just the visible data.  Please see attached Superstore sample workbook.

• ###### 1. Re: Calculating a total for the visible date range

HI Dmitry

I tried couple of things, but could not find if the date range with lookup(min(xx),0) is not static in both sides.

I meant, if the date range is fixed oat the end, this is one options.

Calculate the

Bck-calc cumulative number.

If you set both side as range, only way is to use parameter as long as I could think out.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Calculating a total for the visible date range

And if I make this a bit more complicated by throwing 'Segment' into the Color tile (to make the chart stacked lines/bars)?

• ###### 3. Re: Calculating a total for the visible date range

Definitely, adding dimension to table calc makes solution 10 times more complicated.

Shin

• ###### 4. Re: Calculating a total for the visible date range

Bummer.  The real data actually has a couple of dimensions in the mix.  But thanks, anyway, and thanks for the idea of using start/end labels for the cumulative line.  I'll just go with that solution.