Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sebastian_fager
Contributor III
Contributor III

Pricing index

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..

11 Replies
sunny_talwar

Do you have YearWeek in the future?

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sebastian_fager
Contributor III
Contributor III
Author

No,

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

How should the formula work?

sebastian_fager
Contributor III
Contributor III
Author

.. 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
Contributor III
Contributor III
Author

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:

Price index.png

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

sebastian_fager
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

(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