Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Do you have YearWeek in the future?
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.
No,
Thought max(YearWeek) only took the max YearWeek their are a sale value..
How should the formula work?
.. 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?
Would it be possible to provide a sample to take a look at what you have?
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.
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))
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?
(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