Aggregation & Set analysis queries. Applying past performance to future.

[#1]

Hi

I am trying to use detailed data of past performance and make projections for future.

This is my past sale trend. For a given state, district and product combination, there is the actual volume sold.

Past sale trend
State	District	Product	Volume_sold
S1	D1	P1	100
S1	D1	P2	200
S1	D2	P2	300
S2	D3	P3	400

Now, at a macro level, I have the projection or target volume to be sold at a state level.

Plan for sale future
State	Volume_plan
S1	700
S2	500

I want to do these 2 tables below using pivot table.

Plan break up Districtwise and Productwise
State	District	Volume_plan
S1	D1
S1	D2
S2	D3

State	District	Product	Volume_plan
S1	D1	P1
S1	D1	P2
S1	D2	P2
S2	D3	P3

I am not clear about the use of TOTAL or other Set analysis functions.

Any help would be extremely useful.

Thanks in advance

ragha

[stephencredmond]

Hi,

Something like this?

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

I am CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

Follow me on Twitter: @stephencredmond

[#3]

Thanks Stephen for the response.

But that is not what I was looking for. Sorry for not being clear.

On the basis of past data, I want the projection to be split to the State, District and Product level.

In Excel,

     for the 1st table to be created, I would have used (Volume_plan of S1) * (Volume_sold of S1&D1 / Volume_Sold of S1) = Volume_plan of S1&D1

     for the 2nd table to be created, I would use the above formula to (Volume_plan of S1&D1)*(Volume_sold of S1&D1&P1)/Volume_sold of S1&D1)

I am not sure of how these excel SUMIF and CONCATENATE operations are to be done in qlikview.

Thanks again

ragha

[stephencredmond · Accepted solution]

Hi Ragha,

Volume_plan of S1 = Sum(TOTAL<State> Volume_plan)

Volume_sold of S1&D1 = Sum(TOTAL<State, District> Volume_sold)

Volume_Sold of S1 = Sum(TOTAL<State> Volume_sold)

So, the plan would be: Sum(TOTAL<State> Volume_plan) * Sum(TOTAL<State, District> Volume_sold) / Sum(TOTAL<State> Volume_sold)

Hopefully you should be able to work out the rest yourself?

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

Follow me on Twitter: @stephencredmond

[somenathroy]

PFA .....

Though i hv doubt in your formula. pls check once again.

Regards,

Som

[#6]

Thanks for the response Somenath

Checked the logic. Its actually fine.

Attaching the final file just for the benefit of the community in case someone needs it.

Thanks again for the response.

ragha

[#7]

Hi Stephen

Thanks a lot.

But just before your response was received, I was going through the reference manual and found aggr().

I used Volume_sold of S1&D1 as     AGGR NODISTINCT SUM(Volume_plan, State, District)

On similar lines, remaining formulae also worked out

This seems to work fine too.

Could you please tell me which one is better to use?

Regards

ragha

[stephencredmond]

Hi,

I would always prefer to use Total, unless I need to have a different expression.

Regards,

Stephen