Challanege: Chart accumulation with missing dimension members

[#1]

Hello, I have a similar issue which I could use help with .....

We have date (yyyy-mm-dd) and product-line dimensions, and need to present for each day the total $ bought over all product-lines, as running accumulationover all past dates ( say from 1/1/2010);

The chart must have date on x axis (so its tricky to play with the order of dimensions).

The chart should show the each of the latest 60 days (NOT for the entire history); So there is a selection on the date dimension.

The raw data includes date and $ sales for each product line.

However, on certain dates one (or several) product lines had 0 sales and do not appear in the data. So we have a 'punctured' dimension ....

On those days such product-lines do not get summed at all - yielding a low figure, since the product-line's entire history is omitted from the total on those days.

Any idea of how to avoid punctured dimensions?

(hopefully, without inflating raw data with rows with 0$ for the missing products)

Thanks in advance.

Guy

[fernando_tonial]

Hi, see the exemple.

Uncheck Suppress Zero-Values in the Presentation.

Best Regards.

[#3]

Hello Fernando,

Neat, but two issues.

- We need to present just part of the dates, yet accumulate all the history.In the example chart if I select from 10/2/2011 the purchases before are not accumulated, and the running total is low.

- We actually need to present costs relative to the accumulated sales figure (in %); so accumulation is needed in the denominator. Can we support this  with QV chart "Full Accumulation"?

One approach is to accumulate values in the script (hold accumulated value per each product and date), and skip QV Full Accumulation. This works badly with missing members ...

For more tries, I added Grupo B to your data, and used simpler values. Attached.

Best

Guy

[llauses243]

Hi,

This is my offer, pls to see .zip atached

Good luck, Luis

[#5]

Hello Luis,

THX. I need to look deeper to understand the result here.

Best

Guy

[Jason_Michaelides]

If I understand correctly...

Instead of using the built-in Full Accumulation, use Above() and RangeSum() to accumulate in the expression.

RangeSum(Above(Sum([Valor Venda]),0,RowNo()))

will give you the same as using Full Accumulation.  However, you can now add on to that the Sum([Valor Venda]) for all dates before the selected range:

=RangeSum(Above(Sum([Valor Venda]),0,RowNo()))+Sum(TOTAL {<Data={"<$(=Min(Data))"}>}[Valor Venda])

so your accumulation will now start at the right level.

Hope this helps,

Jason

[#7]

THX a lot, will check this.

My Valor Venda already sums -- in the DB --  sales history of a each product line over the entire history
(say from 1/1/2010).

For each date that we have sales for a certain product line, we sum sales for that product line over the entire history.

In QV, in any date I add Valaor Venda for each of the product-lines and should get the total sales of all products over the entire history! 

That's what I want.

However, on some dates there were sales only for a certain product line; and create in the DB the total over time just for it.

In QV, when I sum the totals (over time) over each product line -- on that date. I miss the entire history of the product lines that did not have sales or totals on that certain date....

Bad

I would like to add the latest availalbe total (as already in the DB) for any product line whose total is missing on that date.

Possible?

Guy

[Jason_Michaelides]

Sorry - I don't understand your question.  Did the above solution not do as you wanted then?  Can you maybe provide some specific examples of what you want to see?