Pricing index

[sebastian_fager]

Hi,

I want to calculate a pricing index.

First i need the latest average price per week per product.

To do that i use this formula:

sum({<YearWeek={$(=max(YearWeek))}>}Sales)

/

sum({<YearWeek={$(=max(YearWeek))}>}Quantity)

The problem is the value in the tabell only shows when i mark a product, why?

And later, I would divide the value above with a stuck zero/starting point to get a index.

How should i do that?

sum({<YearWeek={$(=min(YearWeek))}>}Sales)?

But then i don't know the start week, because it's different between the products..

[sunny_talwar]

Do you have YearWeek in the future?

[jonathandienst]

Sunny has it, I think. You have no sales and/or no quantity for the maximum Yearweek value. When you select a product, the max value gets trimmed down to the max for that product, so you have data for the expression.

[sebastian_fager]

No,

Thought max(YearWeek) only took the max YearWeek their are a sale value..

How should the formula work?

[sebastian_fager]

.. and how do i manage this:

weekly average selling price per product

/

first average weekly selling price for 2016(fixed)

?

*2016: Would it be possible for the user to change the fixed starting point, for example 2015?

[sunny_talwar]

Would it be possible to provide a sample to take a look at what you have?

[sebastian_fager]

Sorry I don't know how to give you a sample without giving you the whole app 😕

This is my formula now:

Index =

(sum(INVOICE_AMOUNT) / sum(INVOICE_QUANTITY))

/

(sum({<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_AMOUNT) / sum({<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_QUANTITY))

But it doesn't work. Gives me this result:

And I want to calculate a weekly index starting from first week (preferably the first value) in 2016.

[sebastian_fager]

If i use this formula it works if a select only one product. But gets wrong if a select more than one.

(sum(INVOICE_AMOUNT) / sum(INVOICE_QUANTITY))

/

(sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_AMOUNT) / sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_QUANTITY))

[sunny_talwar]

I am confused, why are you using the same expression for Numerator and denominator here?

(Sum(INVOICE_AMOUNT) / Sum(INVOICE_QUANTITY)) -> Here both the expressions in Numerator and Denominator are the same? What is the logic here

/

(sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_AMOUNT) / sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_QUANTITY)) ->Same is true here?

[sebastian_fager]

(Sum(INVOICE_AMOUNT) / Sum(INVOICE_QUANTITY)) -> Here both the expressions in Numerator and Denominator are the same? What is the logic here -> Invoice_amount is money, invoice_quantity is how many. So Invoice_amount / invoice_quantity = average price.

/

(sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_AMOUNT) / sum(TOTAL {<[Year]={$(='2016')},Week={$(='01')}>}INVOICE_QUANTITY)) ->Same is true here? This is average price for week 1. Must be fixed/stuck.

So this is:

Average price / Average price week 1 = Price index