Price variation - Count function

[#1]

Hi everyone,

I do use a Count funciton in a KPI object to calculate the number of prices which went up over the laster year but I cannot make it with my current formula!

Count(Avg([Year]={2016})Price >Avg([Year]={2017})Price)

Do you have any tips or advices?

Thanks a lot

Florian

[#2]

Thanks for your help, due to your tips I have indentified the issue and the problem was my dimensions..

I had two dimensions at the beginning but by deleting I have solved the problem

It works using:

Sum(Aggr(If(round(Avg({<[Year]={2016}>}Price),0.01) > round(Avg

({<[.Year]={2017}>}Price),0.01), 1, 0),ItemCode))

You're right, sorry for the formula. I am using this one:

if(round((Avg({1<[Year]={2017} >}Price)-Avg({1<[Year]

={2016} >}Price))/Avg({1<[Year]={2016} >}Price),0.01)>0,lightgreen(),

if(round((Avg({1<[Year]={2017} >}Price)-Avg({1<[Year]

={2016} >}Price))/Avg({1<[Year]={2016} >}Price),0.01)<0,lightred(),))

I doesn't work for value which are really close to zero such as 0.46% of price variation.

Cheers,

Florian

[sunny_talwar]

May be this

If((Avg({1<[Year] = {2017}>} Price) - Avg({1<[Year] = {2016}>} Price))/Avg({1<[Year] = {2016}>} Price) > 0, LightGreen(),

If((Avg({1<[Year] = {2017}>} Price) - Avg({1<[Year] = {2016}>} Price))/Avg({1<[Year] = {2016}>} Price) < 0, LightRed()))

[#4]

This expression works correctly but I was trying to use the round() function because I do get some slightly differences in my prices between 2016 and 2017 and my price variation can end up beeing -0.00%. (-0.0000009%)

What I'm trying to achieve is not to get any color formatting for this number because rounded to 2 decimal it is still 0.00%.

Thanks

Florian

[sunny_talwar]

Since 0.46% = 0.0046, it might make sense to do a rounding for 4 digits

[#6]

I feel really stupid while reading your answer

I have one last question if you have some time left (if you don't know it yet, you're saving my life..)

I want to create a table with the TOP 5 variations upwards and downwards for the items I bought. I am struggling with If and rank function but I am not sure which one I should use.

(Avg({1<[Year]={2017},ItemCode={"rank(

(Avg({1<[Year]={2017} >}Price)-Avg({1<[Year]

={2016} >}Price))

*(Sum({1<[Year]={2016} >}Quantity))

)<=10"} >}Price)-Avg({1<[Year]

={2016} >}Price))/Avg({1<[Year]={2016} >}Price)

Thanks you so much

Florian

[sunny_talwar]

When you say variation, is this your last column (percentage change from 2016 to 2017)?

[#8]

It's the last one on this pic (Delta VA) which equals to Qantity2016*(Price2017-Price2016) so the total amount I have won or lost over one year due to the price movement.

[#9]

I have find a way! Thanks for your time and your help

[marygrace]

I am looking for something like you did. the last 5 prices for each item. Just show if the prices change between purchases. ¿Somebody can help me?