Converting the field value (Quantity) into interval values (Quantity_Range)
Product
Quantity
Quantity_Range
ABC
5
0-5
ABC
10
6-15
ABC
5
16-20
ABC
12
21-32
ABC
3
33-35
ABC
5
35-40
I have a table with 2 fields (product and quantity). I need to create another field which will convert my quantities into a range. Please provide a solution
If your interval period is constant then you can use Class(),
If not then use if() to create bucket
like if(fieldName>0 and fieldName<6,'1-5',
if(fieldName>5 and fieldName<16,'6-15',
if(fieldName>15 and fieldName<21,'16-21','>21'
)))
Regards,
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
will the if condition be suitable? My quantity field values may vary. I need to set the Quantity_Range values with respect to the cumulative sum of the Quantities.
By using this i can calculate the Sum(Price) for the Quantities.
I need to get the following output:
Sales:
Product
Quantity
Quantity_Range
Price
ABC
5
0-5
520
ABC
10
6-15
1100
ABC
5
16-20
600
ABC
12
21-32
1800
ABC
3
33-35
450
ABC
5
35-40
750
where my price is calculated based on the pricing table:
Product:
PRODUCT
START
END
PRICE
ABC
1
3
100
ABC
4
15
110
ABC
16
30
120
ABC
31
40
150
I need to calculate the price based on the Quantities sold in sales table.
In Quantity=5, then Price = 100*3 (First 3 quantities) + 100*2 (remaining 2 quantites) = 520
For Quantity = 10, (It is considered as 15 (5+10) Quantities, but the pricing starts from 6-15 quantities, neglecting the pricing range of the first 5 quantities.) 110*10=1100.
Similarly i need to calculate the price of the remaining Quantity in the sales table.
