topic Re: Alternate states, aggregation and Avg in QlikView

Alternate states, aggregation and Avg

Thu, 23 May 2013 20:39:55 GMT

Hi,

I have a problem solving the below issue.

I need to calculate a variance in price based on the diff between Price from 2 states and a Quantity.

Basic formula: (Price_1 - Price_2) * Quantity

AggregationKey = Dim1 | Dim2 | Dim3 | Dim4 | Dim5 (a key found in both states).

On the AggregationKey there are more then 1 price and I need to take the average of them.

vCommonSelections contains several expressions for selecction in both states (i.e. Dim1 = $:: Dim1, Dim2 = $:: Dim2...)

Expression looks like this:

Sum(
     Aggr((
          Avg({[State_1]<$(vCommonSelections)>} Price) -
          Avg({[State_2]<$(vCommonSelections)>} Price)) *
          Sum({[State_2]<$(vCommonSelections)>} Quantity),
     AggregationKey)
)

AggregationKey has no state but it works fine anyway.

What i need o do now is adding Month to the aggregation. So the expression will look like this...

...and I want to be able to choose different Months in the states e.g. Jan-Mar in State 1 and Apr-Jun in State 2, it will always be the same number of month like in the example 3 month from state 1 and 3 month from state 2.

The aggregation expression will after this not be the same (i know that) e.g. "AggregationKey, Jan" and "AggregationKey, Apr".

Can this be solved? I've tried to rank the months so each state will have 1, 2, 3 as ranked month but i can't get it ti work.

Basically make the Aggrkeys to be the same despite the fact they are'nt...!!!

So, any ideas???

Brg

//K.Arathorn

Re: Alternate states, aggregation and Avg

swuehl — Thu, 23 May 2013 21:32:33 GMT

If each avg(...) function alone does return the correct price results (i.e. reflect the month selection in its state), just take care that your outer sum() is calculated in the context of the full set of all records 1 , like

Sum({1}
     Aggr((
          Avg({[State_1]<$(vCommonSelections)>} Price) -
          Avg({[State_2]<$(vCommonSelections)>} Price)) *
          Sum({[State_2]<$(vCommonSelections)>} Quantity),
     AggregationKey, Month)
)

edit:

of course, if you select disjunct sets of months, you won't get results for both avg() function on the same month. Means, why do you want to group by Month, then?

Might be better, if you can post a small sample (a qvw or a sample of your input data), and what your expected results looks like.

Re: Alternate states, aggregation and Avg

Fri, 24 May 2013 13:21:58 GMT

Hi,

The reason I want to aggregate over month is that I need the average price per month and AggregationKey in my calculation, not the average price for all month selected.

Thus, (Avg(Price1) - Avg(Price2)) * Sum(Quantity2) calculated for each combination of AggregationKey and Month.

I use {[State_1] + [State_1]} instead of {1}.

Brg

//K.Arathorn