topic Re: Sales Value Buckets over time in QlikView

Sales Value Buckets over time

johngouws — Tue, 21 Nov 2023 11:15:22 GMT

Good afternoon.

I have a requirement to show bucketed Sales Values over time. I’ve created the buckets and can get each bucket to return the correct Sales Values. For example.

For testing I’ve created example charts for most of the Buckets that are ‘hard code’. Although the example charts are showing data, they are not 100% correct – until you select the related Bucket.

When one actually selected a Bucket, the correct values show. For example, if you select the 5k-10k bucket the related chart returns the correct Values. All the values for each month per customer ranges between 5000 and 1000.

My question is, I don’t want a chart for each bucket. I need assistance with the expression in a Generic chart that shows the values when a Bucket is selected. I include the xlsx as well as the qvw file. If the explanation needs so clearing up, please feel free to reply.

I would appreciate any guidance on this.

Thank you very much.

Re: Sales Value Buckets over time

marcus_sommer — Wed, 22 Nov 2023 15:34:25 GMT

Your buckets are script-generated against single-values but within the charts you are summing the values and this against several dimensions. This is a logical conflict.

I could imagine that an UI approach with a calculated dimension would be more suitable for your views. Maybe by integrating it directly in the table, like:

It's here simplified done with a class() around an aggr() which ensured that the buckets are calculated in regard to the period-fields and the customer. One customer is selected to show that its sales belong to different buckets.

You will need to play a bit with it to find what do you want to show - each single value, against the customer and/or the periods and/or further fields? Also with the buckets-sizes because class() itself applies only one size at the time whereby this size might be a variable and could then be easily changed. With some rangemin/rangemax wrapping the lower and upper outliers could be fetched and with some more pick(match()) and/or if-loops also the sizes itself whereby it would increase the complexity.

Re: Sales Value Buckets over time

Aasir — Wed, 22 Nov 2023 15:47:04 GMT

Create a calculated dimention and use it in Generic chart
First:

BucketDimension:
LOAD
*,
ApplyMap('BucketMapping', Sales) as Bucket
INLINE [
Bucket, MinRange, MaxRange
1k-5k, 1000, 5000
5k-10k, 5001, 10000
10k-15k, 10001, 15000
// Add more buckets as needed
];

// Create a mapping table for the calculated dimension
BucketMapping:
MAPPING LOAD
Bucket,
Sales
INLINE [
1k-5k, 2500
5k-10k, 7500
10k-15k, 12500
// Add more mappings as needed
];

Then:
in Generics chart
Dimension: Bucket
Expression: Sum(Sales)

Re: Sales Value Buckets over time

johngouws — Sun, 26 Nov 2023 06:07:29 GMT

Thanks Marcus.
It will need some fiddling, but I will be able to get it to work, well enough.

Thanks.