Calculating aggregate sales for 2 different periods using this formula

Wed, 13 Nov 2013 09:19:42 GMT

I want to calculate Aggregate Sales for 2 different time periods using the below formula.

Power((V(tn)/V(to)),1/(tn-to)) - 1

Where

V(tn) = Aggregate Primary Sales Quantity for last period of selection
V(to) = Aggregate Primary Sales Quantity for first period of selection

tn-to = Number of periods(For eg.if I have selected 2012 Q2 and 2013 Q3,so tn-t0 will be 5)

Here tn & t0 can be Year,Quarter and Month

I have written the below Expression but it is not working correctly :

=if(GetSelectedCount(Year)>0,
(pow(sum({<Origin={'PRIMARYSALES'},Year={$(=Max(Year))}>}
Sales_Plan_Qty)/sum({<Origin={'PRIMARYSALES'},Year={$(=Min(Year))}>}
Sales_Plan_Qty),1/((max(Year)-min(Year))))-1),
if(GetSelectedCount(Quarter)>0,
(pow(sum({<Origin={'PRIMARYSALES'},Year={$(=Max(Year))},Quarter={$(=Max(Quarter))}>}
Sales_Plan_Qty)/sum({<Origin={'PRIMARYSALES'},Year={$(=Max(Year))},Quarter={$(=Min(Quarter))}>}
Sales_Plan_Qty),(1/(max({<Year={$(=Max(Year))}>} Quarter)-min({<Year={$(=Max(Year))}>}
Quarter))))-1),
if(GetSelectedCount(Month)>0,
(pow(sum({<Origin={'PRIMARYSALES'},Year={$(=Max(Year))},Quarter={$(=Max(Quarter))},Month={$(=Max(Month))}>}
Sales_Plan_Qty)/sum({<Origin={'PRIMARYSALES'},Year={$(=Max(Year))},Quarter={$(=Min(Quarter))},Month={$(=Min(Month))}>}
Sales_Plan_Qty),1/(max({<Year={$(=Max(Year))}>}
Month)-min({<Year={$(=Max(Year))}>} Month))))-1)))

can anyone please help me out on the above query.

Regards,

hari

Re: Calculating aggregate sales for 2 different periods using this formula

Wed, 13 Nov 2013 10:19:01 GMT

Hi Hari,

First, this forum is about Expressor so I suggest you to move the question to another QlikView forum to be watched by more people.

Anyway, without seen the model and the data you are using it is very difficult to give a response. I suggest to attach an example application with scrambled/fake data to the question.

Regards

topic Calculating aggregate sales for 2 different periods using this formula in QlikView

Calculating aggregate sales for 2 different periods using this formula

Re: Calculating aggregate sales for 2 different periods using this formula