topic Accumulate back with set analysis in QlikView

Accumulate back with set analysis

Fri, 08 Oct 2010 12:58:37 GMT

Hi all,

I have a line chart which simply counts by period (distinct) values. I want to create another expression which is like backward accumulation but with user defined adjustments. I cannot get the expression correct and the attached example hopefully expresses what I am trying to achieve in more detail.

Any help appreciated.

Regards,

Gordon

v9sr4

Accumulate back with set analysis

Fri, 08 Oct 2010 13:41:42 GMT

Hi Gordon,

I think the easiest is to change the script and calculate the number of machines per month in it.

Then add a ration field to use the factor depending on the month.

You'll find enclosed an update of your document.

I hope it help !

Best regards,

Sébastien

Accumulate back with set analysis

Fri, 08 Oct 2010 13:43:02 GMT

Here is the file

Accumulate back with set analysis

Fri, 08 Oct 2010 14:01:39 GMT

Sebastien,

Thanks for the reply. Unfortunately the counts cannot be pre-calculated - this is only an example but in reality there are many different fields that can be selected and so the counts are dynamic.

Regards,

Gordon

Accumulate back with set analysis

johnw — Sat, 09 Oct 2010 00:14:32 GMT

I don't know if I understand the question. But if I do, one solution is an AsOf table connecting each month to the correct current and previous months, and giving a denominator for that specific connection. Then aggregate by the connected months to get the final totals. See attached.

Set analysis can't solve it since a set is only calculated once for the chart, not once per dimension value.

Accumulate back with set analysis

Mon, 11 Oct 2010 11:15:22 GMT

Thats excellent, John!

My users wanted to dynamically change the 'denominator' values but I am trying to convince them its not possible outside a load. The only thing I can think is to have them stored in an xls they can maintain and then force a reload.

Once again, thank you for your energy!

Regards,

Gordon

Accumulate back with set analysis

johnw — Mon, 11 Oct 2010 19:32:03 GMT


gordon.savage wrote:My users wanted to dynamically change the 'denominator' values but I am trying to convince them its not possible outside a load.

It's possible. I didn't quite pick up on that being what you meant in the original question. Attached is one solution. I've put the Denominator field in a separate table so that it isn't repeated unnecessarily, and turned it into an inputfield. Then I manipulate a chart to only show the denominators for WtyMth, since I don't want them changing the raw data or accumulation. Then I allow input of the data by using an inputsum(Denominator) for the expression. In the example, I only allow entry for the most recent 10 months, but this could be expanded to whatever you need. A 0 value appears to exclude the month instead of causing a division by 0, simplifying things.

There's probably some way to avoid the inputsum() and still allow input of the fields for just WtyMth. A table box avoids the inputsum(), but I don't think can exclude the other accumulation types. And just having Denominator as an expression didn't seem to allow input. I don't have a lot of experience with input fields, so I'm just guessing that what I have could be simplified.

Accumulate back with set analysis

Tue, 12 Oct 2010 09:45:52 GMT

Awesome John, just awesome.

Thanks again.

Gordon