topic Re: Using aggregated values in calculations in QlikView

Using aggregated values in calculations

Sat, 18 Jun 2016 18:30:25 GMT

Please help with the problem i have see the qvw attached.

I have to calculate dynamically if a customer is 'Web' or 'Offline' and then use the resulting figures for further calculation.

This is based on the number of Orders they have placed and where within the selected time period - if > 75% of orders are online they are classed as online, <75% = offline

I have used aggregation to show the split and calculate the growth % vs previous year.

What I am stuck on is how to use the results of my aggregation for further calculations

I need to show the difference between the 2 growth figures Online = 11.56% and 55.34 to give me 43.78, but I don't know how to use these values against each other.

Please help.

Thanks

Re: Using aggregated values in calculations

swuehl — Sat, 18 Jun 2016 19:13:12 GMT

Maybe like attached?

Re: Using aggregated values in calculations

sunny_talwar — Sun, 19 Jun 2016 02:02:59 GMT

Not sure where you are wanting to do this, You can also try this if you want this as the third row in the chart

Script Changes: Added a new Island table:

Dim:

LOAD * Inline [

Dim

];

Dimension:

=Pick(Dim, IF(Aggr(SUM({<[Branch] ={"$(='Web')"}>} [CountOrders])

SUM([CountOrders]), [Customer]) < 0.75, Dual('Offline Growth', 1), Dual('Online Growth', 2)), Dual('Difference', 3))

Expression:

=Pick(Dim,

(SUM({<[Year] ={$(=MAX(Year))}>} Sales )

- SUM({<[Year] ={$(=MAX(Year)-1)}>} Sales )

)

SUM({<[Year] ={$(=MAX(Year)-1)}>} Sales ),

RangeSum(Above([Growth Yr v Yr-1], 1), -Above([Growth Yr v Yr-1], 2)))

Re: Using aggregated values in calculations

Mon, 20 Jun 2016 11:45:31 GMT

This gives me the figure, as an alternative way to the above solution.

Very useful way to approach to get the answer

Help is much appreciated

Re: Using aggregated values in calculations

Mon, 20 Jun 2016 11:47:05 GMT

This is exactly what I needed, I was approaching it from the wrong angle, using set analysis works perfectly for what I need.

Thanks, your help is much appreciated

Re: Using aggregated values in calculations

Mon, 20 Jun 2016 11:47:21 GMT

Thanks for the input and help