topic Re: Challanege: Chart accumulation with missing dimension members in QlikView

Challanege: Chart accumulation with missing dimension members

Thu, 10 May 2012 15:16:44 GMT

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

Re: Challanege: Chart accumulation with missing dimension members

fernando_tonial — Thu, 10 May 2012 19:58:31 GMT

Hi, see the exemple.

Uncheck Suppress Zero-Values in the Presentation.

Best Regards.

Re: Challanege: Chart accumulation with missing dimension members

Fri, 11 May 2012 18:17:28 GMT

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

Re: Challanege: Chart accumulation with missing dimension members

llauses243 — Sat, 12 May 2012 09:33:05 GMT

Hi,

This is my offer, pls to see .zip atached

Good luck, Luis

Re: Challanege: Chart accumulation with missing dimension members

Tue, 22 May 2012 09:25:16 GMT

Hello Luis,

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

Best

Guy

Re: Challanege: Chart accumulation with missing dimension members

Jason_Michaelides — Tue, 22 May 2012 09:54:29 GMT

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

Re: Challanege: Chart accumulation with missing dimension members

Tue, 22 May 2012 11:21:47 GMT

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

Re: Challanege: Chart accumulation with missing dimension members

Jason_Michaelides — Wed, 23 May 2012 07:48:28 GMT

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?