Organic & Inorganic growth - possible with set expression ?

[#1]

Hi all

I am trying to write an expression for Organic / Inorganic growth defined as follows:

If a certain outlet / customer has purchased the same material in the same period last year then it is 'organic growth', if the outlet has purchased a material this year but not in same period last year or has purchased that same material in same period last year but not in same period this year then it is 'inorganic growth'.

Now as the periods that can be selected are dynamic the user could select an entire year or as little as a single day, the expression will need to check the same period last year and tell whether that same outlet purchased the same material.

Do you guys think this is possible to do using a set expression or will it have to be done in the script.  I'm hoping to avoid using the script due to the fact that the period comparison can vary so much.

I would also prefer a solution that does not rely on certain dimensions (Outlet and Material) appearing in the chart as we are using cyclic groups to see this Organic / Inorganic growth by several other dimensions.

Thanks

[#2]

Hey,

It sounds like something that should be possible.

You have to find a way to check if a customer bought something in the period a year before and if the customer bought something this periode.

Have have a look at this file:

http://community.qlik.com/qlikviews/1175

Hope this helps.

gr.

Frank

[#3]

Thanks, however my problem is not the same period last year part of the set expression, it is the match between  the same material and outlet between the 2 comparison periods.

I already have working set expressions to do the SPLY comparison...

[#4]

So the user selectes a period and then for that period you want to check if a certaint product is bought in an outlet in both periods or one of the periods?

How do you want to show the results? How many times it happend? If it happened?

Maybe it's easier if you could upload an example with some (fake) data?.

[#5]

Yes you are correct.

The results are sums of quantities to be included or excluded based on whether they are organic or not.

Here is an example:

Outlet ABC bought 10 cases of product XYX on 3rd August 2011.  Outlet ABC also bought 3 cases of product XYX on 30th July 2012.  If the user selects the period from 30th July to 3rd August 2012 then the algorithm will check whether any of product XYX was bought by Outlet ABC in this period 2012 and the same period 2011.  If there are amounts >0 in both periods then it is considered Organic, if the user only selects 30th July then it wil be Inorganic.

So if I select Organic (through using variable) it will only show the sums where there are the same products sold to the same outlets in both the selected period and also the same period last year.  If Inorganic is selected then sums of product sales where the same outlet did not buy the same product in same period last year.

[swuehl]

I think this should be possible using set analysis.

In your example:

>If the user selects the period from 30th July to 3rd August 2012 then the algorithm will check whether any of product XYX was bought by Outlet ABC in this period 2012 and the same period 2011.

What is the result for Organic? 3 or 13?

Summing only the organic amount for the selected period or across the selected and previous period?

And what about inorganic?

[#7]

Do you use Outlet as and products as dimensions?

If so, can you use something like:

if(vVariable='Organic' and sum(amount)>0 and sum({Set analysis part for year earlier} amount)>0,

sum(amount)+sum({Set analysis part for year earlier} amount),

if(vVariable='Inorganic' and sum(amount)<=0 and sum({Set analysis part for year earlier} amount)>0,

sum({Set analysis part for year earlier} amount)>0

))

[#8]

frankcrezee wrote:

Do you use Outlet as and products as dimensions?

If so, can't you use something like:

if(vVariable='Organic' and sum(amount)>0 and sum({Set analysis part for year earlier} amount)>0,

sum(amount)+sum({Set analysis part for year earlier} amount),

if(vVariable='Inorganic' and sum(amount)<=0 and sum({Set analysis part for year earlier} amount)>0,

sum({Set analysis part for year earlier} amount)>0

))

Although I do have Outlet and Product as fields I do not want to use them as dimensions in the actual chart.  It is fairly easy to do if I could but the client needs to measure this growth across many other dimensions but always keeping in account the fact that you need to 'match' the outlets and products with the same outlets and products in the same period last year (SPLY).

[#9]

swuehl wrote:

I think this should be possible using set analysis.

In your example:

>If the user selects the period from 30th July to 3rd August 2012 then the algorithm will check whether any of product XYX was bought by Outlet ABC in this period 2012 and the same period 2011.

What is the result for Organic? 3 or 13?

Summing only the organic amount for the selected period or across the selected and previous period?

And what about inorganic?

In the example the Organic result will be 13 (sum of) and the Inorganic result will be 0.

However if the date selection is changed to only be from the 30th of July to the 2nd of August then the Organic result will be 0 and the Inorganic result will be 3.

So to summarise - summing of organic results in both periods and summing of inorganic results in both periods and the thing that differentiates them is whether the same outlet bought the same product in both periods.  It does not matter how much was bought as long as it is >0.

[#10]

So I think I have it:

Organic sales this year:

sum({<[DimDateID]={'>=$(vMinDate)<=$(vMaxDate)'}>}

     Aggr(IF(sum({<[DimDateID]={'>=$(vMinDate)<=$(vMaxDate)'}>} Sales)>0

     AND sum({<[DimDateID]={'>=$(vMinDateLY)<=$(vMaxDateLY)'}>} Sales)>0,

     sum({<[DimDateID]={'>=$(vMinDate)<=$(vMaxDate)'}>} Sales)),Outlet, Product))

Organic sales last year:

sum({<[DimDateID]={'>=$(vMinDateLY)<=$(vMaxDateLY)'}>}

     Aggr(IF(sum({<[DimDateID]={'>=$(vMinDate)<=$(vMaxDate)'}>} Sales)>0

     AND sum({<[DimDateID]={'>=$(vMinDateLY)<=$(vMaxDateLY)'}>} Sales)>0,

     sum({<[DimDateID]={'>=$(vMinDateLY)<=$(vMaxDateLY)'}>} Sales)),Outlet, Product))

Therefore Organic growth is first expression minus second expression.

DimDateID is simply the correctly formatted date field, the variables are simply max(DimDateID) and min (DimDateID) as well as the last year versions addyears(max(DimDateID),-1) and addyears(min(DimDateID),-1).

Tentative integrity checking is showing up reasonable answers so will confirm this when testing has been completed.  The Inorganic growth calculations are much the same except the IF condition checks that one year is 0 and other has figures.

Thanks for looking into this swuehl and frankcrezee