19 Replies Latest reply: Mar 27, 2012 4:26 PM by Sebastian Pereira

# sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi community!

I need a kind of a stack in qlikview,...

I have a table with amount + date, which is chronologically.

I want a new field, where all items  within the 200 bucks limit(chronologically) have an discount of %10, otherwise 1% discount)

This doesn't work: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

in Qlikview I can not use sum() within an if statement

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you want this in the  script? This is, add a new fild on your table with the new price?

If(amount<200, amount*0.1, amount*0.01) as NewPrice

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

If this is in the script you can only do sums if it has a group by clause.

If it isn't in the script you might be looking for aggr,

sum(aggr(if(sum(amount)<200,sum(amount/10),sum(amount/100)),item))

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

No, it's hard to explain

The first products ( Date - chronologically )  which are in summary under 200 should get an discount of 10%, the rest should get only 1%.
Yes it's in the script

Hopefully you unterstood me
Thanks and regards

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you have a group by clause? You will need to do a resident load Group by section to be able to use sum in the script.

I do not really understand the

The first products ( Date - chronologically )

part. Is it only a certian amount of products that you want this applied to, or all products where the total sum is under 200?

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

ok for example:

Limit: 200

Table:  Amount    -  Date

product1: 50   -  1.2.2012

product2: 60   -  3.2.2012

product3: 80   -  4.2.2012

product4: 40   -  5.2.2012

product5: 10   -  6.2.2012
====================

240

so products 1,2,3 should get 10% discount ( first products wich are under the limit = 200 )

product 4 +5 should  get 1% discount.

How do I realize it?

This way is much better for me:

The summary of the value which is under 200 should get 10%  and the rest value (40 ) should get 1%

So  the first product ( 1,2,3,  1/4 of 4 ) should get 10%

the rest ( 3/4 of product 4, product 5 ) should get 1%.

That means:  total value of 200 gets 10%

the rest value ( 40) gets 1%

I know that I have to use group by,  but if i combine Sum()  and an if statement  it gives me an error...

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

I am not sure if this is possible in the script, if it is then it is beyond my skill level.

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

yes that's right, normaly I would need somethink like a stack and put one by one out, to fill the limit of 200,  but in qlikview it's totally difficult to realize that.

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you have a set number of products you are working with, and will it always be product 1 on day 1 product 2 day 2 ect.?

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi, try:

Products:

Product,

Amount,

Price,

Date,

RowNo() as Ord

Resident ProductsTemp

Order By Date Asc;

Let Acc = 0;

For A = 1 to NoOfRows('Products')

Let PrAmount = Peek('Amount',\$(A),'Products');

Let Acc = \$(Acc) + \$(PrAmount);

If \$(Acc)>200 then

Let OrdLim = Peek('Ord',\$(A),'Products');

Exit For;

End If;

Next;

Left Join (Products)

*,

If (Ord>=\$(OrdLim), 0.09, 0.9)*Price as NewPrice;

Resident Products;

Please, correct sintaxys if im wrong, because im writing directly here.

Tell me if it works.

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi, try the below.

, NumSum(Amount, Peek('Sum_Amount')) as Sum_Amount
, If(NumSum(Amount, Peek('Sum_Amount')) < 200, Price * 0.9, Price * 0.99) as New_Price
Resident Products
Order By Date asc;

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

jykang, sorry, but the field Sum_Amount is created at the end of the sentence. You can`t reference it inside the sentence.

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

sebastiandpereira wrote:

jykang, sorry, but the field Sum_Amount is created at the end of the sentence. You can`t reference it inside the sentence.

Actually, yes you can.

Jykang's basic solution is sound, is how I independently thought to solve it when I saw the question, and it works with some minor fixes to the actual script.  The reason it can work is that the peek() function is looking at the record you created before the current record, which does have a Sum_Amount established, so we're still following the basic principle of "you must establish a field before you can reference it", no matter how funny the script might look.  It makes me sad how often I see people reject a correct solution, without trying it, because of something they think they know about how QlikView works.  OK, yes, 90% of the time, you'll probably be right.  But the other 10% of the time, you'll learn something new.  It's that 10% of the time that's so valuable.

Anyway, the script needs a little modification to do the partial discounting that was mentioned, and that'll be easer if we use a preceding load.  This seems to work:

,if(Cumulative<=200,Amount*.9
,if(peek('Cumulative')>=200,Amount*.99
,(200-peek('Cumulative'))*.9+(Cumulative-200)*.99)) as "Discounted Amount"
;
,rangesum(Amount,peek('Cumulative')) as Cumulative
INLINE [
Product, Amount, Date
product1, 50, 1.2.2012
product2, 60, 3.2.2012
product3, 80, 4.2.2012
product4, 40, 5.2.2012
product5, 10, 6.2.2012
];

See attached.

All that said, I don't think QlikView should be doing any accounting for you, and I consider calculating discounts to be accounting.  That's what an accounting system is for.  QlikView is just a reporting system.

(Edit: Added single quotes in the peek() function above.  It's supposed to be a text literal.  QlikView interprets it correctly without the single quotes, but I think a good habit is to ALWAYS use single quotes for text literals.)

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

thanks guys! I'll try it on monday,  sounds good

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi, everyone,

I think John's solution is more sophisticated than mine.
And I am very impressed with his talk: It's that 10% of the time that's so valuable.
Thanks John!

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Jykang, excuse me, i didn't want to be offensive (sorry for my english). Please, sorry.

John, you are right, I didn't test the Jykang comment. But, why you don't just comment your solution? I know you are the best in qlikview who really know how the qlikview works, and I prefer you to explain the changes in Jykang solution (I am really interested in this script, is easier than my solution with For), than read about how the people must write in forums.

And, excuse me for make you sad... It was only a comment... Make sad because of hunger in the world!!

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

sebastiandpereira wrote:

...I prefer you to explain the changes in Jykang solution (I am really interested in this script, is easier than my solution with For)...

You mean explain how it works, and the minor differences between my and jykang's solution?

The script processes records in order, so we start at the first record.  We do a peek(Cumulative) even though we've never established a value for that field.  One thing that jykang got right and I got wrong is that I don't have single quotes - the parameter here is a text literal, not a field.  I try to make a habit of always putting text literals in single quotes, even where QlikView allows me to leave the quotes off.  So whoops.  Anyway, instead of an error, peek('Cumulative') returns null when we're on the first row.  And unlike previous(), peek() refers to your output table instead of the input table, and can thus refer to fields you are creating as part of the output table.  The null doesn't appear to be documented, and the "output table" part appears to be incorrectly documented, but it's this behavior that makes peek() so valuable in many cases, such as this one.

Mind you, you're probably taking a risk using peek() this way, because some day some QlikTech employee may look at where the documentation says "in the input table" (emphasis added), compare it to the actual behavior, and then "correct" the actual behavior to match the documentation.  But I'm hoping QlikTech is smarter than that, and simply fixes the documentation, since the current behavior is why peek() is such a great function.

I used rangesum() instead of numsum() because, according to the help text, "The numsum function is now obsolete and is superseded by the rangesum function."  I used rangesum() intead of a simple + sign because we know that on the first record, we want to take only the Amount value, and ignore the Cumulative value, which is null.  Adding to a null always creates another null.  But rangesum treats null as 0, so it does what we need.  We could have done something like Amount + if(recno()>1,peek('Cumulative'),0), but that's more complicated.  That's the basic idea of what we're doing, though, and might arguably be easier for someone to maintain because it's more clear what's happening.

Jykang also used a field Price, which might well be correct in the real data, but wasn't in the sample data.  To keep it simple, I used Amount everywhere.

I didn't include an order by.  We'll probably need one in the real data, but I don't know what order we want.  Perhaps it's by date or more likely by a timestamp.  Perhaps it's something else.  Since I didn't know, I left it off.  Perhaps I should have included it like jykang did to indicate that one would likely be required.

The other difference is to handle the discounted amount only applying to the first 200, even if that meant it applied to part of record and not the whole thing.  I suspect there's a cleaner way, but I checked for that case explicitly, and then split the amount into what was before and after 200 so that I could apply the correct discount to each part.

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Thanks for the patience John...

Sorry if I'm doing a stupid question, but i can't understand why you use two sentences:

Include....;

, Jykang used only one sentence. What it means? You are Loading from nothing? What QV does between both tables? Join ? Concat?

I'm sure that it was a fool question, sorry.

In second place, are you talking about the use of "peek" within the "For"? I have used like that a lot of times... I have understand that this sintaxis were correct, because help says that. What another suggestions do you have about my answer? I would like to know other opinions, because i use loops a lot of times...

Thanks again

• ###### Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

In my case, I used a preceding load to refer to field "Cumulative" in the preceding load.  I could have done it all in one load by repeating the rangesum(Amount,peek('Cumulative')) part of the expression a couple times, but I prefer to not write the same code in multiple places where I have a reasonable alternative.  You can see how jykang was forced into repeating his NumSum(Amount,Peek('Sum_Amount')) since he only used a single load statement.  It's not really a big deal either way for an expression this simple, particularly since you can visually stack one on top of the other to make it very clear that if one changes, so should the other.  I do it both ways, whichever seems cleaner to me for the specific case.

Any reference I made to peek() was in reference to my or jykang's code.  I was not referring to your code.

Looking at your code, your idea certainly seems sound, though I think I see a couple simple bugs.  Testing it out now.  Yeah, your code works fine with a few trivial modifications.  When using peek(), records are numbered starting with 0, so you need to address that in some way, such as by looping from 0 to the number of rows - 1.  You need 0.99 instead of 0.09.  And you should drop ProductsTemp at some point.  And of course it doesn't handle that borderline case of splitting the discount across the 40 amount, but you probably weren't trying to do so.  As far as suggestions, I'd just suggest that you don't need a loop in this case, that there is a simpler solution.

I almost never use loops, or at least not explicit loops like that.  I do use a few while loops, but I feel like those fit more cleanly into the load structure.  I'm not saying that there's anything wrong with explicit loops when they're called for, just that there may often be a simpler alternative.

You might also try some performance testing if you find what seems to be an alternative to one of your loops.  I can't remember performance testing it myself, but my guess is that the explicit loop is slower than some alternatives.  On the other hand, I think I recall making the same prediction about while loops, and performance testing showing that I was wrong, that they are extremely fast.  I like to think that I'm right more often than wrong with my performance predictions, but I'm wrong very, very often.  Testing is always called for when performance is critical.

One reason I don't typically like loops is that for large amounts of data, they can overwhelm the logfile.  In this case, though, we're not going to have to loop through many records before exiting the loop, and seeing the loop details in the log may actually be quite useful for debugging.  So in this specific case, I'd consider that an advantage of your approach.

• ###### sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

John, thanks you very much for the explaining.

I found very useful the Preceding Load !, i gues that it would help me in some cases.

About Loops, I have used it when I didn't found one solution...

For example. One customer had needed to clasify a group of customers in 4 categories (A, B, C and D), in accordance of their sales in last 28 days (A most sales, D less sales). They doesn't had an algoritm, becaouse of they have used her common sense. Later than a time of analysis, my solution was:

- The limit between B and C is the Median (not Average, because in some cases, the most sellers were the 70% of toe total sales). So, since the Median Value, the limit could be higher or lower, searching a heavy slope

- Limit between B and A is calculated with the heavier slope between 1rst place and the B-C limit.

- Limit between D and C is calculated with the heavier slope between last place and the B-C limit.

- Every slope is weighted with two variables (up and down) that depends on the difference with last or next, and their sales. This is to make a stable measurement.

- I did that with 3 loops (with IF, Load, Peek's, etc...). Each one sets the limits.

- Then, one load get the group to each customer.

Do you have some similar case? Now, for the same people, i'm doing a Stock Redistribution App, who manages the stock for their subsidiaries... there are a lot of Bussiness rules!!! Is a kind of AI, who will decide the best strategy between most cases...

So, thanks John!!!   And I repeat, i don't wonted to make you sad... sorry for this.

Sebastián.