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

[#1]

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

[jvitantonio]

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

[#3]

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

[#4]

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

[#5]

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?

[#6]

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

[#7]

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

[#8]

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.

[#9]

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.?

[sebastiandperei]

Hi, try:

Products:

Load

     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)

Load

     *,

     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.