# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
Specialist

## Calculate Rangesum till 20 and create sets

Hi All,

I'm trying to create sets of data of weight 20.

1. I want to sort data by hour and Zone and create sets with in each hour and Zone.

2. Create Buckets with weight 20 or close to 20 in every hour and Zone.

Please see attached QV. Any help is highly appreciated.

Here is my sample data with 'Bucket' as output:

 Time, Product, Zone, Weight Bucket 7, P1, 1, 2 1 7, P2, 1, 3 7, P3, 1, 5 7, P4, 1, 1 7, P5, 2, 16 2 7, P6, 2, 8 3 7, P7, 2, 12 7, P8, 2, 15 5 7, P9, 2, 17 6 7, P10, 2, 19 7 7, P11, 2, 12 8 7, P12, 2, 11 9 7, P13, 2, 8
Labels (3)

• ### rangesum

1 Solution

Accepted Solutions
MVP

Not so straight forward for me to understand... but see if this is what you want

``````T1:
Time, Product, Zone, Weight
7, P1, 1, 2
7, P2, 1, 2
7, P3, 1, 3
7, P4, 1, 1
7, P5, 2, 16
7, P6, 2, 8
7, P7, 2, 12
7, P8, 2, 15
7, P9, 2, 17
7, P10, 2, 19
7, P11, 2, 12
7, P12, 2, 11
7, P13, 2, 8
7, P14, 3, 12
7, P15, 3, 45
7, P16, 3, 67
7, P17, 3, 9
7, P18, 3, 12
7, P19, 3, 32
7, P20, 3, 8
8, P21, 1, 12
8, P22, 1, 45
8, P23, 1, 67
8, P24, 1, 9
8, P25, 2, 12
8, P26, 2, 32
8, P27, 2, 8
8, P28, 2, 12
8, P29, 2, 45
8, P30, 2, 67
8, P31, 2, 9
8, P32, 2, 12
8, P33, 2, 32
8, P34, 2, 8
];

T2:
If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, RangeSum(Weight, Peek('Cum_Weight')), Weight) as Cum_Weight,
If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, Peek('Bucket'), RangeSum(1, Peek('Bucket'))) as Bucket
Resident T1
Order By Time, Zone, Product;

DROP Table T1;

Left Join (T2)
1 as Flag
Where Max_Cum_Weight >= 10 and Max_Cum_Weight <= 20;
Max(Cum_Weight) as Max_Cum_Weight
Resident T2
Group By Bucket;

T3:
Product,
Zone,
Weight,
Cum_Weight,
If(Flag = 1, Bucket) as Bucket
Resident T2;

DROP Table T2;``````
12 Replies
MVP

Do this in the script or front end?

Specialist
Author

I wanted to do this in Script please.

MVP

May be this

``````T1:
Time, Product, Zone, Weight
7, P1, 1, 2
7, P2, 1, 3
7, P3, 1, 5
7, P4, 1, 1
7, P5, 2, 16
7, P6, 2, 8
7, P7, 2, 12
7, P8, 2, 15
7, P9, 2, 17
7, P10, 2, 19
7, P11, 2, 12
7, P12, 2, 11
7, P13, 2, 8
7, P14, 3, 12
7, P15, 3, 45
7, P16, 3, 67
7, P17, 3, 9
7, P18, 3, 12
7, P19, 3, 32
7, P20, 3, 8
8, P21, 1, 12
8, P22, 1, 45
8, P23, 1, 67
8, P24, 1, 9
8, P25, 2, 12
8, P26, 2, 32
8, P27, 2, 8
8, P28, 2, 12
8, P29, 2, 45
8, P30, 2, 67
8, P31, 2, 9
8, P32, 2, 12
8, P33, 2, 32
8, P34, 2, 8
];

T2:
If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, RangeSum(Weight, Peek('Cum_Weight')), Weight) as Cum_Weight,
If(Time = Previous(Time) and Zone = Previous(Zone) and RangeSum(Weight, Peek('Cum_Weight')) <= 20, Peek('Bucket'), RangeSum(1, Peek('Bucket'))) as Bucket
Resident T1
Order By Time, Zone, Product;

DROP Table T1;``````
Support (Former)

Mark, did Sunny's post help you get what you needed?  If so, please be sure to use the Accept as Solution button on the post that worked to give Sunny credit and let others know what worked.  If you are still trying to get things to work, leave us an update on where things stand.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Specialist
Author

Hi Sunny,

Apologize for late reply. This is perfect!

One final request - How do i set minimum threshold limit to create a bucket.

Max is 20, but if i want to set Min as 10 for creation of bucket, can you please help?

Specialist
Author

Hi Brett, I apologize for late reply. I'm travelling and not able to get back to it.

Thanks!

MVP

What would the output look like for this new condition?

Specialist
Author

Only creates buckets between 10 and 20.

MVP

Not sure I follow when you say the bucket should be between 10 and 20?

Community Browser