topic Re: Aggregation & Set analysis queries. Applying past performance to future. in QlikView

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

Thu, 20 Jun 2013 14:50:50 GMT

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

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

stephencredmond — Thu, 20 Jun 2013 15:53:03 GMT

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

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

Thu, 20 Jun 2013 16:32:30 GMT

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

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

stephencredmond — Fri, 21 Jun 2013 10:12:16 GMT

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

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

somenathroy — Fri, 21 Jun 2013 13:06:25 GMT

PFA .....

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

Regards,

Som

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

Sat, 22 Jun 2013 05:58:47 GMT

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

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

Sat, 22 Jun 2013 06:07:37 GMT

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

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

stephencredmond — Sat, 22 Jun 2013 14:39:19 GMT

Hi,

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

Regards,

Stephen