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

[sunny_talwar]

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

[sunny_talwar]

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

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

[#4]

Hi Sunny,

Thank you for your answer and your reactivity

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

[#5]

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

[sunny_talwar]

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

[#7]

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

[sunny_talwar · Accepted solution]

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

[#9]

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

[sunny_talwar]

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