How to mix aggr() and if statements correctly - price effect calculation

[#1]

I need to find the correct function to obtain the attached results.

In red you will find the expected results.

To make a long story short, I need to find the to eliminate the rows in which ther's not a qty, sales or prices for both the analysed period and calculate correctly the price effect and the related %.

Thanks in advance for your support!!

Mb

[sunny_talwar]

Click on the 'Use advanced editor'

and then click on 'Attach' at the bottom

[#3]

Ciao Sunny,

I hope this time works!!

thanks!

ciao

mb

[sunny_talwar]

Yup, I see the attachment now

Let me check it out.

Best,

Sunny

[sunny_talwar]

Why did you leave this out of your calculation for article 2???? Is there a reason you left it?

[#6]

Ciao Sunny

The point is that, in general, every combination  customer - article that have  a price or sales or quantity (<>0) only for one of the 2 periods (2014, 2015) must be excluded from the calculation.

I need to consider combination customer - article when there is a price or sales or quantity (<>0)  for both periods.

This to avoid any "mix" effect and obtain the correct pure "price effect", aggregated by article as in the third sheet.

Ciao

Thanks in advance,

Mb

[sunny_talwar]

Here you go, I hope this gives you what you are looking for.

Best,

Sunny

[#8]

Ciao Sunny!!

great!!

However my boss sends me a database without column of prices, but I need to recaluclate it.

I reattach a third version of the file.

Could you please give me a support also for this case?

Why you use only 2014 <>0 and not both years?

Thanks again

ciao

Marcello

[sunny_talwar]

Used only 2014 for check because on most of the places we are multiplying with the 2015 qty. If 2015 qty is 0, the whole thing will be 0

[sunny_talwar]

Here is the script changes (So instead of a given price, you calculate it in the script using sales/qty.

Table:

LOAD *,

  sales/qty as Price;

LOAD customer,

    sku,

    date,

    qty,

    sales

FROM

Community_167219.xlsx

(ooxml, embedded labels, table is [ORIGINAL DATABASE]);

Nothing on the front changes.

I hope this will resolve all the issues for you

Best,

Sunny