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: 
Anonymous
Not applicable

IF statements

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

Doing this in the script or front end object?

Anonymous
Not applicable
Author

I was planning to do it in the expression editor

marcus_malinow
Partner - Specialist III
Partner - Specialist III

how about something like this:

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

+

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

sunny_talwar

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
Not applicable
Author

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