topic Re: Animated Bubble Graph with Accumulative Sum in QlikView

Animated Bubble Graph with Accumulative Sum

Tue, 04 Mar 2014 19:15:05 GMT

I have a bubble graph with three expressions and one dimension:

X-axis: total price -> sum(Price)
Y-axis: price per unit -> sum(Price)/sum(Quantity)
Bubble: total Quantity -> sum(Quantity)
Dimension: Category

Now I want to animate this bubble graph based on time("period").

I want to use the accumulated Price and Quantity to calculate the price per unit. However, the "Full Accumulation" in the Expressions tab adds up the price per unit for each period, which is not what I want. I came across a great post in the Qlikview Community, and tried the following expression for the Y-axis:

sum( aggr( rangesum( above( sum(price),0,3) ),Period, Category)) / sum( aggr( rangesum( above( sum(Quantity),0,3) ),Period, Category))

But this gives me the price per unit based on each period's data instead of cumulative data. I have attached a qlikview example for reference. I would like the animated graph to resemble the total price graph on top when the animation reached period 3 (currently, the price per unit is not showing correctly).

Any help would be greatly appreciated!

(the link to the accumlated sums post: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums )

Re: Animated Bubble Graph with Accumulative Sum

orabrabbo — Thu, 06 Mar 2014 11:58:11 GMT

hello! I have attached the file with the solution to your needs. I hope my solution can help you ...

Re: Animated Bubble Graph with Accumulative Sum

Thu, 06 Mar 2014 19:50:01 GMT

Thank you for your response.

One more question: If I have more than 3 period, do I just keep adding "+rangesum(above(total sum(Price),x,1))" to the formula, where x = number of category?

Also I notice that in my real data set, the order of the category is inconsistant. Is there a SORT function I could use to sort the order of the category in the formula?

I really appreciate your help. Thank you.

Re: Animated Bubble Graph with Accumulative Sum

orabrabbo — Fri, 07 Mar 2014 09:14:32 GMT

My solution can not be configured to dynamically manage the period.

Usually in these cases, it is preferable to pre-calculate the cumulative using the script loaded.

This is an example:

load

if (Previous (K1 & K3 & K4 & K6) = (K1 & K3 & K4 & K6)

[Population served in the month] + peek ('Cumulative Population Served Month'), [Population served in the month]

)

as [Cumulative Population Served Month]

Resident ABITANTI_COMUNI_TEMP2

order by K1, K3, K4, K6, K2;

I hope I was helpful

Re: Animated Bubble Graph with Accumulative Sum

Thu, 13 Mar 2014 19:26:23 GMT

How does the following line works if my data is updating in the same table for every period?

Previous (K1 & K3 & K4 & K6) = (K1 & K3 & K4 & K6)

Also, do you have Qlikview example for the scirpt you mentioned above?

Thousands thanks!

Re: Animated Bubble Graph with Accumulative Sum

orabrabbo — Wed, 19 Mar 2014 14:33:03 GMT

hello,

I have attached an example.

See you soon !