17 Replies Latest reply: Jul 14, 2017 10:13 AM by Florian Saner

# Price variation - Count function

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

• ###### Re: Price variation - Count function

May be this

Sum(Aggr(If(Avg({<[Year]={2016}>}Price) > Avg({<[Year]={2017}>}Price), 1, 0), ProductID))

Assuming we are talking about the prices for a product. Change this to a field for which you are doing the price comparison

• ###### Re: Price variation - Count function

For prices that went up... you might need this

Sum(Aggr(If(Avg({<[Year]={2016}>}Price) < Avg({<[Year]={2017}>}Price), 1, 0), ProductID))

• ###### Re: Price variation - Count function

Hi Sunny,

I do get a figure in my KPI but which does not work exactly. I do have a lot of mising data for 2017 and my KPI takes some of them into account. Shall I include an IF within my current function to sort this out?

Thank you again

Cheers

Florian

• ###### Re: Price variation - Count function

Look at the two selected fields, one of them gives me the result "greater than" and the second "lower than" and I need them to be ricorded as equal.

It might be an very small difference or simply the function which doesn't work as hoped

Best,

Florian

• ###### Re: Price variation - Count function

I am not completely sure I understand what you are trying to convey here. Can you elaborate?

• ###### Re: Price variation - Count function

Hi Sunny,

So you might be confused with the names "Greater than" & "Lower than". My PivotTable aims to reflect the number of prices which are going up (greater than) and down (lower than)  between 2016 (1st column) and 2017 (2nd column).

I am trying to find the correct formula to display this result within my two KPI boxes.

Do you have an idea how I could edit my formula to get the correct answer?

Thanks,

Florian

• ###### Re: Price variation - Count function

What is the expression you are using? May be you need to use Rounding here? Round to 2 digits for matching?

• ###### Re: Price variation - Count function

Yeah thank you, it seems to work for these references but my formulation takes sometime the null value into account(as zero value I guess).

If you have a look at the following table you will see one single selected field which should not increase my Delflation KPI. Do you have an idea how to solve this? Using a null() function might be appropriate in this case, right?

My KPI formula:

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

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

I tried to insert a null() function but it doesn't really work:

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

(Avg({<[Year]={2016}>}Price),0.01)) < if(null

(Avg({<[Year]={2017}>}Price)),0, round(Avg

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

Moreover, I am using this expression for the color format. If you look at my table the Delta (-0.46%) is not displayed in red as I wish, is the round() function not used correctly?

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

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

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

It would be wonderful if you had an idea how I could get out this mess

Thanks a lot

Florian

• ###### Re: Price variation - Count function

May be try this

Sum(Aggr(If(Round(Avg({<[Year] = {2016}>} Price), 0.01) > Round(Avg({<[Year] = {2017}>} Price), 0.01) and Len(Trim(Avg({<[Year]={2016}>}Price))) > 0 and Len(Trim(Avg({<[Year]={2016}>}Price))) > 0, 1, 0), Supplier))

Where are you specifying the red color in this expression? All I see is black()?

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

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

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

• ###### Re: Price variation - Count function

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

• ###### Re: Price variation - Count function

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()))

• ###### Re: Price variation - Count function

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

• ###### Re: Price variation - Count function

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

• ###### Re: Price variation - Count function

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

• ###### Re: Price variation - Count function

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

• ###### Re: Price variation - Count function

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.

• ###### Re: Price variation - Count function

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