IF statements

[Anonymous]

Hi,

I have the following problem:

I get 45$ in provision for the first 1250 sales of the year, and 5$ for everything above that. How do I calculate this per month?

It should be.

jan. 169 sales, provision= 169*45=7 605

feb. 1379 sales, provision= 1081*45 + 298*5 = 50 135

mar. 772 sales, provision= 772*5= 38 600

My problem is february, when I Reach 1250 sales, and only get 5$ for sale nr. 1251..

Jonathan

[sunny_talwar]

Doing this in the script or front end object?

[Anonymous]

I was planning to do it in the expression editor

[marcus_malinow]

how about something like this:

(rangemin(sum(sales), 1250) * 45)

+

(rangemax(sum(sales) - 1250, 0) * 5)

[sunny_talwar · Accepted solution]

Great idea, but it seems that Month is in the dimension, so we may have to do some kind of accumulation here

If(RangeSum(Above(Sum(Sales), 0, RowNo())) < 1250, Sum(Sales) * 45,

If(RangeSum(Above(Sum(Sales), 0, RowNo())) >= 1250,

     If(Above(RangeSum(Above(Sum(Sales), 0, RowNo()))) < 1250, ((1250 -  Above(RangeSum(Above(Sum(Sales), 0, RowNo())))) * 45) + ((Sum(Sales) - (1250 -  Above(RangeSum(Above(Sum(Sales), 0, RowNo()))))) * 5),

Sum(Sales) * 5)))

[Anonymous]

Hi again, Sunny. Thanks for help regarding this issue earlier. Very helpful! 😃 Now I need a KPI with this calculation on last months figure. It sees I cannot use the same set analysis doing this. Any suggestions? I need [last month]=(1) - figure.

Best regards Jonathan.

[sunny_talwar]

What is the expected number you are looking to get? It has been a while, so it might help if you can either create a new thread or elaborate a little bit